1

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?

  • 1
    Please, check this other post: Cycles or multiple cascade paths with on delete set null: really? – Ronaldo Apr 12 at 15:00
  • I see, one of the answers is also suggesting a workaround by using a trigger. I'm new to SQL Server and it seems very strange to me that there is no for each row trigger, would have made my life so much easier! – Julius Rupšys Apr 12 at 22:28
  • You can replicate a "for each row" trigger with a cursor inside a statement trigger, but it's a best-practice to operate on sets of rows instead row-by-row processing. – David Browne - Microsoft Apr 13 at 18:40
3

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
| improve this answer | |

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.