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,
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
)
If you have SQL Server 2008, use from HierarchyID type for this work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With