Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths

Question

So while publishing my database scripts in Visual Studio, I get this exact error: Introducing FOREIGN KEY constraint 'FK_Users_Manager' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

This is my simplified script for creating a self referencing Users table:

CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL IDENTITY,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE SET NULL
)

Even if somehow I made a cycle on these relationships, I don't see why wouldn't deleted row's direct children have their ManagerId set to NULL?

Should I create a trigger for this scenario to set NULLs for me instead? Or is there something I am clearly missing?

Answer 1

You can use an INSTEAD OF trigger here, or have logic that re-assigns all the direct-reports prior to deleting a user. EG

--drop table if exists users
go
CREATE TABLE [dbo].[Users]
(
    [UserId] INT NOT NULL,
    [ManagerId] INT NULL,
    [Data] NVARCHAR(1024) NULL,
    CONSTRAINT [PK_Users_UserID] PRIMARY KEY ([UserId] ASC),
    CONSTRAINT [FK_Users_Manager] FOREIGN KEY ([ManagerId]) REFERENCES [Users] ([UserId]) ON DELETE NO ACTION
)

go
create trigger tg_users_del on [Users] instead of delete
as
begin
  set nocount on;
  update Users set ManagerId = null where ManagerId in (select UserID from deleted);
  delete from Users where UserId in (select UserID from deleted);
end

GO
INSERT INTO Users(UserId, ManagerId) values (1,null),(2,1),(3,1),(4,2)

DELETE FROM Users where UserId = 1

select * from Users