Archive for March 3rd, 2010
From Stack Overflow:
I have an SQL Server table defined as below:
TestComposite id (PK) siteUrl (PK) name parentId 1 site1 Item1 NULL 2 site1 Item2 NULL 3 site1 Folder1 NULL 4 site1 Folder1.Item1 3 5 site1 Folder1.Item2 3 6 site1 Folder1.Folder1 3 7 site1 Folder1.Folder1.Item1 6
Items and folders are stored inside the same table
If an item is inside a folder, the
parentIDcolumn is the
idof the folder.
I would like to be able to
DELETE CASCADEitems/folders when I delete a folder.
I tried to define 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.
SQL Server does support chained
CASCADE updates, but does not allow one table to participate more that once in a chain (i. e. does not allow loops).
SQL Server, unlike most other engines, optimizes cascading DML operations to be set-based which requires building a cycle-free DML order (which you can observe in the execution plan). With the loops, that would not be possible.
However, it is possible to define such a constraint without cascading operations, and with a little effort it is possible to delete a whole tree branch at once.
Let's create a sample table:
Read the rest of this entry »