I have an SQL table defined as below:
我有一个SQL表定义如下:
CREATE TABLE [TestComposite] (
ID int,
SiteUrl nvarchar(255),
Name nvarchar(max) NOT NULL,
ParentID int NULL,
PRIMARY KEY (ID, SiteUrl)
);
Items and folders are stored inside the same table, if an item is inside a folder, the ParentID column is the ID of the folder. And I would like to be able to delete CASCADE items/folders when I delete a folder.
项目和文件夹存储在同一个表中,如果项目位于文件夹内,则ParentID列是文件夹的ID。我希望能够在删除文件夹时删除CASCADE项目/文件夹。
An example may be more explicit:
一个例子可能更明确:
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...
So if I delete the item 3 (a folder), I want the items/folders 4, 5, 6 and 7 to be deleted too.
因此,如果我删除项目3(文件夹),我也希望删除项目/文件夹4,5,6和7。
I tried to add a constraint similar to:
我试图添加一个类似于的约束:
ALTER TABLE [TestComposite]
ADD CONSTRAINT fk_parentid
FOREIGN KEY (ParentID, SiteUrl)
REFERENCES [TestComposite] (ID, SiteUrl) ON DELETE CASCADE;
But it gives me this error:
Introducing FOREIGN KEY constraint 'fk_parentid' on table 'TestComposite' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
但它给了我这个错误:在表'TestComposite'上引入FOREIGN KEY约束'fk_parentid'可能会导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION,或修改其他FOREIGN KEY约束。
I also tried to add a second SiteUrl column named ParentSiteUrl, in case the problem was that a column couln't be part of the same FK/PK, but I have the same error message.
我还尝试添加名为ParentSiteUrl的第二个SiteUrl列,以防问题是列不属于同一个FK / PK,但我有相同的错误消息。
Am i doing something wrong?
难道我做错了什么?
Thank you,
4 个解决方案
#1
6
Create an ON DELETE NO ACTION
constraint and use this to delete all records and their children:
创建一个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
)
#2
1
If you have SQL Server 2008, use from HierarchyID type for this work.
如果您有SQL Server 2008,请使用HierarchyID类型进行此项工作。
#3
0
I think what you want to do can be achieved by adding a new column called ParentId, and then declare it as foreign key with primary key. That way the problem will be solved and you can still do everything you want
我想你可以通过添加一个名为ParentId的新列来实现你想做的事情,然后将它声明为带有主键的外键。这样问题就会解决,你仍然可以做你想做的一切
#4
0
The problem is that you create possibility of recursive cascade - when every deleted by cascade can create any number of subsequent deletes. MS SQL doesn't support it. Try to delete them in your code manually. BTW i don't recommend cascade deletes.
问题是你创建了递归级联的可能性 - 当每个被级联删除时都可以创建任意数量的后续删除。 MS SQL不支持它。尝试手动删除代码中的它们。顺便说一句,我不建议级联删除。
#1
6
Create an ON DELETE NO ACTION
constraint and use this to delete all records and their children:
创建一个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
)
#2
1
If you have SQL Server 2008, use from HierarchyID type for this work.
如果您有SQL Server 2008,请使用HierarchyID类型进行此项工作。
#3
0
I think what you want to do can be achieved by adding a new column called ParentId, and then declare it as foreign key with primary key. That way the problem will be solved and you can still do everything you want
我想你可以通过添加一个名为ParentId的新列来实现你想做的事情,然后将它声明为带有主键的外键。这样问题就会解决,你仍然可以做你想做的一切
#4
0
The problem is that you create possibility of recursive cascade - when every deleted by cascade can create any number of subsequent deletes. MS SQL doesn't support it. Try to delete them in your code manually. BTW i don't recommend cascade deletes.
问题是你创建了递归级联的可能性 - 当每个被级联删除时都可以创建任意数量的后续删除。 MS SQL不支持它。尝试手动删除代码中的它们。顺便说一句,我不建议级联删除。