SQL Server: deleting with self-referential FOREIGN KEY at EXPLAIN EXTENDED

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 parentID column is the id of the folder.

I would like to be able to DELETE CASCADE items/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.

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

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:

Table creation details

CREATE SCHEMA [20100303_cascade]
CREATE TABLE TestComposite (
id INT NOT NULL,
siteUrl NVARCHAR(255) NOT NULL,
name NVARCHAR(MAX) NOT NULL,
parentId INT NULL,
PRIMARY KEY (id, siteUrl)
);
GO
BEGIN TRANSACTION
DECLARE @cnt INT
SET @cnt = 0
WHILE @cnt &lt; 50000
BEGIN
        INSERT
        INTO    [20100303_cascade].TestComposite (id, siteUrl, name, parentID)
        VALUES  (
                @cnt / 10 + 1,
                'site' + CAST((@cnt % 10 + 1) AS NVARCHAR(255)),
                'name ' + CAST(@cnt / 10 + 1 AS NVARCHAR(MAX)),
                CASE WHEN @cnt &lt; 50 THEN NULL ELSE @cnt / 50 END
                )
        SET @cnt = @cnt + 1
END
COMMIT
GO

This table contains 50,000 records forming a hierarchy.

If we try to delete an entry that has some children, we'll fail:

DELETE
FROM    [20100303_cascade].TestComposite
WHERE   id = 42
AND siteUrl = 'site1'

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "fk_TestComposite_self". The conflict occurred in database "test", table "20100303_cascade.TestComposite".
The statement has been terminated.

To delete an item and all of its children, we should build a hierarchical query to retrieve the whole branch, and delete the branch all at once.

To do it, we just semi-join the table to the results of the recursive CTE:

WITH    q AS
(
SELECT  id, siteUrl
FROM    [20100303_cascade].TestComposite
WHERE   id = 42
AND siteUrl = 'site1'
UNION ALL
SELECT  tc.id, tc.siteUrl
FROM    q
JOIN    [20100303_cascade].TestComposite tc
ON      tc.parentID = q.id
AND tc.siteUrl = q.siteUrl
)
DELETE
FROM    [20100303_cascade].TestComposite
OUTPUT  DELETED.*
WHERE   EXISTS
(
SELECT  id, siteUrl
INTERSECT
SELECT  id, siteUrl
FROM    q
)

View query results

id	siteUrl	name	parentId
42	site1	name 42	8
211	site1	name 211	42
212	site1	name 212	42
213	site1	name 213	42
214	site1	name 214	42
215	site1	name 215	42
1056	site1	name 1056	211
1057	site1	name 1057	211
1058	site1	name 1058	211
1059	site1	name 1059	211
1060	site1	name 1060	211
1061	site1	name 1061	212
1062	site1	name 1062	212
1063	site1	name 1063	212
1064	site1	name 1064	212
1065	site1	name 1065	212
1066	site1	name 1066	213
1067	site1	name 1067	213
1068	site1	name 1068	213
1069	site1	name 1069	213
1070	site1	name 1070	213
1071	site1	name 1071	214
1072	site1	name 1072	214
1073	site1	name 1073	214
1074	site1	name 1074	214
1075	site1	name 1075	214
1076	site1	name 1076	215
1077	site1	name 1077	215
1078	site1	name 1078	215
1079	site1	name 1079	215
1080	site1	name 1080	215
31 rows fetched in 0.0038s (0.0234s)

SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 1 ms.
Table 'TestComposite'. Scan count 62, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 5, logical reads 256, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:   CPU time = 16 ms,  elapsed time = 4 ms.

  |--Sequence
       |--Table Spool
       |    |--Clustered Index Delete(OBJECT:([test].[20100303_cascade].[TestComposite].[PK__TestComposite__78E9C54B]), OBJECT:([test].[20100303_cascade].[TestComposite].[ix_TestComposite_parent_site]))
       |         |--Top(ROWCOUNT est 0)
       |              |--Sort(DISTINCT ORDER BY:([test].[20100303_cascade].[TestComposite].[id] ASC, [test].[20100303_cascade].[TestComposite].[siteUrl] ASC))
       |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Recr1010], [Recr1011]))
       |                        |--Index Spool(WITH STACK)
       |                        |    |--Concatenation
       |                        |         |--Compute Scalar(DEFINE:([Expr1019]=(0)))
       |                        |         |    |--Clustered Index Seek(OBJECT:([test].[20100303_cascade].[TestComposite].[PK__TestComposite__78E9C54B]), SEEK:([test].[20100303_cascade].[TestComposite].[id]=(42) AND [test].[20100303_cascade].[TestComposite].[siteUrl]=N'site1') ORDERED FORWARD)
       |                        |         |--Assert(WHERE:(CASE WHEN [Expr1021]>(100) THEN (0) ELSE NULL END))
       |                        |              |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1021], [Recr1006], [Recr1007]))
       |                        |                   |--Compute Scalar(DEFINE:([Expr1021]=[Expr1020]+(1)))
       |                        |                   |    |--Table Spool(WITH STACK)
       |                        |                   |--Index Seek(OBJECT:([test].[20100303_cascade].[TestComposite].[ix_TestComposite_parent_site] AS [tc]), SEEK:([tc].[parentId]=[Recr1006] AND [tc].[siteUrl]=[Recr1007]) ORDERED FORWARD)
       |                        |--Clustered Index Seek(OBJECT:([test].[20100303_cascade].[TestComposite].[PK__TestComposite__78E9C54B]), SEEK:([test].[20100303_cascade].[TestComposite].[id]=[Recr1010] AND [test].[20100303_cascade].[TestComposite].[siteUrl]=[Recr1011]) ORDERED FORWARD)
       |--Table Spool
       |--Assert(WHERE:(CASE WHEN NOT [Expr1018] IS NULL THEN (0) ELSE NULL END))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([test].[20100303_cascade].[TestComposite].[id], [test].[20100303_cascade].[TestComposite].[siteUrl], [Expr1023]) WITH UNORDERED PREFETCH, DEFINE:([Expr1018] = [PROBE VALUE]))
                 |--Table Spool
                 |--Index Seek(OBJECT:([test].[20100303_cascade].[TestComposite].[ix_TestComposite_parent_site]), SEEK:([test].[20100303_cascade].[TestComposite].[parentId]=[test].[20100303_cascade].[TestComposite].[id] AND [test].[20100303_cascade].[TestComposite].[siteUrl]=[test].[20100303_cascade].[TestComposite].[siteUrl]) ORDERED FORWARD)

We see that the whole branch of 31 records was deleted all at once, without violating the FOREIGN KEY.

Unlike some other systems, we don't have to worry about the order the records are deleted, since all SQL Server referential constraints are deferred till the end of the query.

Written by Quassnoi

March 3rd, 2010 at 11:00 pm

Posted in SQL Server

8 Responses to 'SQL Server: deleting with self-referential FOREIGN KEY'

Subscribe to comments with RSS

In my case the query goes to infinite loop,
If I use option (maxrecursion 365) or (maxrecursion 3267)
any value it fails with error as reached highest recusion value . If I use 0 which is infinite the query runs for an hour with no output.

Jay

7 Jul 14 at 11:02
This means you have loops in your data.

Quassnoi

7 Jul 14 at 16:16
Hi Quassnoi,

Any suggestion on how to avoid this situation?
I understand it data related issue which is causing infinite loop, But I cannot control the data being sent to the DB . Any other way we can handle it?

Thanks,
Jay

Jay

9 Jul 14 at 09:35
@Jay: https://explainextended.com/2014/07/10/sql-server-deleting-with-self-referential-foreign-key-handling-loops/

Quassnoi

10 Jul 14 at 21:29
Can you please explain the last part? Is it the same as:

WHERE id in (SELECT id from q)

?

Henry Ho

16 Sep 14 at 05:15
Excellent work maan!! Thank you so mutch.

Luk

26 Jan 17 at 12:52
thanks! Was just looking to validate some code I inherited which included deletion of table records with self-referencing Id in hierarchy structure. Was concerned why there wasn’t any sorting that deleted lowest children first, but this article helped me understand why that doesn’t matter.

mike dugan

10 Jun 19 at 22:08
Awesome post! Thanks for sharing the knowledge and keep up the good work.

Josh

28 Dec 21 at 10:44

EXPLAIN EXTENDED