Cycles or multiple cascade paths with on delete set null: really?

Question

I'm asking this question to check if my reasoning about cascaded delete is correct and if I'm not overlooking anything. I understand the behavior and I'm not asking why it is implemented with the current restrictions.

When we try to create a table containing a self reference like this:

CREATE TABLE [BlogComments] (
    [Id] int NOT NULL IDENTITY,
    [AuthorName] nvarchar(100) NULL,
    [Content] nvarchar(max) NULL,
    [CreatedTime] datetime2 NOT NULL,
    [ReplyToId] int NULL,
    CONSTRAINT [PK_BlogComments] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BlogComments_BlogComments_ReplyToId] FOREIGN KEY ([ReplyToId])
         REFERENCES [BlogComments] ([Id]) ON DELETE SET NULL -- Not: CASCADE
);

We get the infamous exception

Introducing FOREIGN KEY constraint 'FK_BlogComments_BlogComments_ReplyToId' on table 'BlogComments' may cause cycles or multiple cascade paths.

I understand that the setting ON DELETE SET CASCADE may actually cause a recursive cascade of deletes and, hence, may be dangerous, or time consuming at best. But ON DELETE SET NULL is different: it will only nullify ReplyToId of direct child records, not of their child records.

So am I right in concluding that SQL Server is overly restrictive when the foreign key is defined with ON DELETE SET NULL? Or am I overlooking some good reasons for this restriction in this particular case?

By the way, I'm aware of a school of thought that contends that there shouldn't be any cascade actions anyway. Let's not get into that.

Answer 1

From Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

If a self reference with on delete set null was allowed by SQL Server the query plan need to have two different operator that changes the same table. First one that deletes the rows, store the deleted rows in a spool and the uses that spool to find the rows that needs to be updated. If you have a row where ReplyId and Id is the same it will try to update the value of a row that is already deleted. Or at least it will be once the transaction is committed.

I don't say it is impossible to implement this only that it is a lot more complicated than one might think at first sight. Think about how to handle halloween protection and add triggers and indexed views to the mix and it quickly goes into something not trivial to build the plan that does the job transactionally correct.

Answer 2

If this error message did not occur, and ON DELETE SET NULL were to set a column to null, any child-rows referencing the old value would no longer have a valid parent row. Cascading the update to null for all child rows would be required to meet relational integrity requirements. However, cascading might result in a never-ending loop, which is the bit that is not supported. This has nothing to do with a self-reference, and is only about the possibility for SQL Server to follow a never-ending loop.

Consider this setup:

DROP TABLE IF EXISTS dbo.CascadeDeletes;
GO

CREATE TABLE dbo.CascadeDeletes
(
    PrimaryID int NOT NULL
        CONSTRAINT PK_CascadeDeletes
        PRIMARY KEY CLUSTERED
    , SubordinateID int NULL
        CONSTRAINT FK_CascadeDeletes_SubID
        FOREIGN KEY
        REFERENCES dbo.CascadeDeletes (PrimaryID)
) ON [PRIMARY];

Insert some sample data; effectively every row is the parent to the next row.

INSERT INTO dbo.CascadeDeletes (PrimaryID, SubordinateID)
VALUES (1, NULL)
    , (2, 1)
    , (3, 2)
    , (4, 3);

Now, update the first row so it's "parent" is the last row:

UPDATE dbo.CascadeDeletes
SET SubordinateID = 4
WHERE PrimaryID = 1;

Consider that a recursive CTE on this table would enter an endless loop:

;WITH rCTE AS
(
    SELECT cd.PrimaryID, cd.SubordinateID
    FROM dbo.CascadeDeletes cd
    WHERE cd.PrimaryID = 1
    UNION ALL
    SELECT ce.PrimaryID, ce.SubordinateID
    FROM dbo.CascadeDeletes ce
        INNER JOIN rCTE ON ce.SubordinateID = rcte.PrimaryID
)
SELECT *
FROM rCTE

Traversing the tree from primary to subordinate is now a never-ending loop, resulting in this error:

Msg 530, Level 16, State 1, Line 27
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

And deleting any row from the table becomes impossible.

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

Msg 547, Level 16, State 0, Line 27
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_CascadeDeletes_SubID". The conflict occurred in database "tempdb", table "dbo.CascadeDeletes", column 'SubordinateID'.

If SQL Server would allow us to define the table so that ON DELETE SET NULL was in effect, deleting any row would update other rows referenced by the deleted row to NULL. Quite clearly, this does not produce an "endless" loop because only a single set of rows will ever be updated. i.e. if the row with PrimaryID = 2 is deleted, the only other row to be cascade-updated would be the row with PrimaryID = 3. No endless loop.

Even though in our simple example, there is no endless loop that could be encountered, I expect the CREATE TABLE and ALTER TABLE code-paths generate an error 1785 for every ON UPDATE or ON DELETE action, whenever the pattern looks like it could be cyclical, that is not NO ACTION, simply to err on the side of caution.

One might consider this a bug.

However, luckily, it is pretty easy to work around this bug with the "old way" of enforcing RI; that is with a trigger:

CREATE TRIGGER CascadeDeletes_OnDeleteSetNull
ON dbo.CascadeDeletes
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.CascadeDeletes
    SET SubordinateID = NULL
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.SubordinateID = d.PrimaryID;
    DELETE 
    FROM dbo.CascadeDeletes
    FROM dbo.CascadeDeletes cd
        INNER JOIN deleted d ON cd.PrimaryID = d.PrimaryID;
END

Now, when you delete a row from our table, as in:

DELETE FROM dbo.CascadeDeletes
WHERE dbo.CascadeDeletes.PrimaryID = 1;

The row is deleted, and related rows have their SubordinateID set to NULL:

╔═══════════╦═══════════════╗
║ PrimaryID ║ SubordinateID ║
╠═══════════╬═══════════════╣
║         2 ║ NULL          ║
║         3 ║ 2             ║
║         4 ║ 3             ║
╚═══════════╩═══════════════╝