sql - 复合主键和外键约束错误

Question

我有一个 SQL 表定义如下:

CREATE TABLE [TestComposite] (  
    ID int,  
    SiteUrl nvarchar(255),  
    Name nvarchar(max) NOT NULL,  
    ParentID int NULL,  
    PRIMARY KEY (ID, SiteUrl)  
);

项目和文件夹存储在同一个表中，如果项目位于文件夹内，则 ParentID 列是文件夹的 ID。我希望在删除文件夹时能够删除 CASCADE 项目/文件夹。

一个例子可能更明确:

INSERT INTO [TestComposite] VALUES (1, 'site1', 'Item1', NULL)
INSERT INTO [TestComposite] VALUES (2, 'site1', 'Item2', NULL)
INSERT INTO [TestComposite] VALUES (3, 'site1', 'Folder1', NULL)
INSERT INTO [TestComposite] VALUES (4, 'site1', 'Folder1.Item1', 3)
INSERT INTO [TestComposite] VALUES (5, 'site1', 'Folder1.Item2', 3)
INSERT INTO [TestComposite] VALUES (6, 'site1', 'Folder1.Folder1', 3)
INSERT INTO [TestComposite] VALUES (7, 'site1', 'Folder1.Folder1.Item1', 6)
etc...

因此，如果我删除项目 3(文件夹)，我希望项目/文件夹 4、5、6 和 7 也被删除。

我尝试添加类似于以下内容的约束:

ALTER TABLE [TestComposite] 
ADD CONSTRAINT fk_parentid 
FOREIGN KEY (ParentID, SiteUrl) 
REFERENCES [TestComposite] (ID, SiteUrl) ON DELETE CASCADE;

但它给了我这个错误:
在表“TestComposite”上引入 FOREIGN KEY 约束“fk_parentid”可能会导致循环或多个级联路径。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。

我还尝试添加第二个名为 ParentSiteUrl 的 SiteUrl 列，以防问题是列不能成为同一 FK/PK 的一部分，但我有相同的错误消息。

我做错了什么吗？

谢谢，

Answer 1

创建一个 ON DELETE NO ACTION 约束并使用它来删除所有记录及其子记录:

WITH    q AS
        (
        SELECT  id, SiteURL
        FROM    TestComposite
        WHERE   id = 3
                AND SiteURL = 'site1'
        UNION ALL
        SELECT  tc.id, tc.SiteURL
        FROM    q
        JOIN    TestComposite tc
        ON      tc.ParentID = q.Id
                AND tc.SiteURL = q.SiteURL
        )
DELETE
FROM    TestComposite
WHERE   EXISTS
        (
        SELECT  id, SiteURL
        INTERSECT
        SELECT  *
        FROM    q
        )