Reputation: 205
I have an SQL table defined as below:
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.
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.
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.
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.
Am i doing something wrong?
Thank you,
Upvotes: 5
Views: 4613
Reputation: 425843
Create an ON DELETE NO ACTION
constraint and use this to delete all records and their children:
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
)
Upvotes: 6
Reputation: 22950
If you have SQL Server 2008, use from HierarchyID type for this work.
Upvotes: 1
Reputation: 60115
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.
http://support.microsoft.com/kb/321843
Upvotes: 0
Reputation: 5806
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
Upvotes: 0