SQL Server – Handling Cycles or Multiple Cascade Paths with ON DELETE SET NULL

cascadesql server

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.

Best Answer

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.

Related Question