Cycles or multiple cascade paths with on delete set null: really? 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.
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             ║
╚═══════════╩═══════════════╝