MS SQL中的自引用约束

Is it true that MS SQL restrict self-referencing constraints with ON DELETE CASCADE option? I have a table with parent-child relation, PARENT_ID column is foreign key for ID. Creating it with ON DELETE CASCADE option causes error

MS SQL是否使用ON DELETE CASCADE选项限制自引用约束？我有一个父子关系表，PARENT_ID列是ID的外键。使用ON DELETE CASCADE选项创建它会导致错误

"Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

“引入FOREIGN KEY约束可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。”

I can't believe that I have to delete this hierarchy in recursive mode. Is there any issue except triggers?

我无法相信我必须在递归模式下删除此层次结构。除了触发器有什么问题吗？

3 个解决方案

#1

It is the case that you cannot set up ON DELETE CASCADE on a table with self-referencing constraints. There is a potential of cyclical logic problems, hence it won't allow it.

在这种情况下，您无法在具有自引用约束的表上设置ON DELETE CASCADE。存在潜在的周期性逻辑问题，因此它不会允许它。

There's a good article here - though it's for version 8 rather than 9 of SQL - though the same rules apply.

这里有一篇很好的文章 - 尽管它适用于版本8而不是9版本的SQL - 虽然适用相同的规则。

#2

I just answered another question where this question was bound as duplicate. I think it's worth to place my answer here too:

我刚刚回答了另一个问题，这个问题被重复了。我认为值得在这里提出我的答案：

This is not possible. You can solve this with an INSTEAD OF TRIGGER

这不可能。你可以通过INSTEAD OF TRIGGER来解决这个问题

create table locations 
(
    id int identity(1, 1),
    name varchar(255) not null,
    parent_id int,

    constraint pk__locations
        primary key clustered (id)

)
GO

INSERT INTO locations(name,parent_id)  VALUES
 ('world',null)
,('Europe',1)
,('Asia',1)
,('France',2)
,('Paris',4)
,('Lyon',4);
GO

--This trigger will use a recursive CTE to get all IDs following all ids you are deleting. These IDs are deleted.

- 此触发器将使用递归CTE来获取您要删除的所有ID之后的所有ID。这些ID将被删除。

CREATE TRIGGER dbo.DeleteCascadeLocations ON locations
INSTEAD OF DELETE 
AS
BEGIN
    WITH recCTE AS
    (
        SELECT id,parent_id
        FROM deleted

        UNION ALL

        SELECT nxt.id,nxt.parent_id
        FROM recCTE AS prv
        INNER JOIN locations AS nxt ON nxt.parent_id=prv.id
    )
    DELETE FROM locations WHERE id IN(SELECT id FROM recCTE);
END
GO

--Test it here, try with different IDs. You can try WHERE id IN(4,3) also...

- 在这里测试，尝试使用不同的ID。您可以尝试WHERE id IN（4,3）...

SELECT * FROM locations;

DELETE FROM locations WHERE id=4;

SELECT * FROM locations
GO

--Clean-Up (Carefull with real data!)

- 清理（仔细查看真实数据！）

if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME='locations')
---DROP TABLE locations;

#3

CREATE TRIGGER MyTable_OnDelete ON MyTable
INSTEAD OF DELETE
AS 
BEGIN

  SET NOCOUNT ON;

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.ParentId

  DELETE FROM mt
  FROM   deleted AS D
  JOIN   MyTable AS mt
  ON     d.Id = mt.Id

END

#1