SQL Server error: introducing foreign key constraint on table may cause cycles or multiple cascade paths

Question

I have two tables, User and Data. For audit purposes, I have two columns added in the Datatable:AddedByandLastModifiedBy`.

Both columns reference the User table and its UserId column. I have a On delete set null constraint set on both the foreign keys. SQL Server does not let me create the table Data and throws the error:

Introducing FOREIGN KEY constraint 'FK__Data__LastModifi__0A9D95DB' on table 'Data' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

These are the SQL scripts:

CREATE TABLE [User]
( 
    UserId int PRIMARY KEY IDENTITY(1,1),
    Name varchar(100) not null
)
 
CREATE TABLE [Data]
(
    Id int PRIMARY KEY IDENTITY(1,1),
    A int,
    B varchar(10),
    AddedBy int 
        FOREIGN KEY REFERENCES [User](UserId) 
                ON DELETE SET NULL,
    LastModifiedBy int 
        FOREIGN KEY REFERENCES [User](UserId) 
                ON DELETE SET NULL
)

Removing the ON DELETE constraint solves the problem, but the question why can't I have two ON DELETE SET NULL conditions?

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-07-06T20:31:46

why can't I have two ON DELETE SET NULL conditions?

Because that would cause multiple cascade paths, and SQL Server simply doesn't support that.

There's an outstanding feedback request for this and to allow cascading in parent-child relationships here.

As a workaround you can either make one of the relationships ON DELETE NO ACTION, or use an INSTEAD OF DELETE trigger on USER that handles the cascading behavior.

SQL Server error: introducing foreign key constraint on table may cause cycles or multiple cascade paths

1 Answers