4

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.

4

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.

  • The complicated plan and the circular reference (ReplyId = Id) may indeed be significant stumbling blocks for the query optimizer. However, I don't see how halloween protection comes into play here. A row, once updated, won't ever be a candidate for another update, would it? At least you made me aware of much more going on under the hood than meets the eye. I'll leave this question open for a while and accept your answer if nothing happens. – Gert Arnold May 14 '18 at 13:07
  • 1
    @GertArnold Well, a row once deleted may later be updated or perhaps the other way around depending on how the hypothetical plan will pan out. – Mikael Eriksson May 14 '18 at 13:22
  • That's true, a row can be the target of two possible actions. The query plan would always have to figure out which action should prevail. If that's also part of the loosely defined "haloween problem", fine with me. Anyway, it's a complication an RDBMS developer isn't waiting for I reckon. – Gert Arnold May 14 '18 at 13:29
  • 3
    @GertArnold They (the SQL Server developers) are a bit prickly about double updating rows. At least they are in "modern times". MERGE has a runtime error dealing with that. "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows." – Mikael Eriksson May 14 '18 at 13:33
3

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             ║
╚═══════════╩═══════════════╝
  • 3
    "Deleting any row becomes impossible" Right. But deleting a whole "cycle" is still possible!: dbfiddle.uk/… – ypercubeᵀᴹ May 14 '18 at 18:51
  • Indeed I think that the possibility of cycles in references (that, as you say, can also occur in cross references) is a major complication. In fact, Mikael also pointed that out in the smallest possible cycle: PrimaryID = SubordinateID. I also think that his argument of multiple operators holds water though. By the way, the cyclic self reference isn't just an academic case or a bug. The not-null fetishists among us define root parents as rows having PK = FK. – Gert Arnold May 15 '18 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.