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
Should I create a trigger for this scenario to set
NULLs for me instead? Or is there something I am clearly missing?