Sql-Server
在表“X”上引入 FOREIGN KEY 約束“FK_X_Y”可能會導致循環或多個級 聯路徑
因此,在 Visual Studio 中發布我的數據庫腳本時,我得到了這個確切的錯誤:在表 ‘Users’ 上引入 FOREIGN KEY 約束 ‘FK_Users_Manager’ 可能會導致循環或多個級聯路徑。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION,或修改其他 FOREIGN KEY 約束。
這是我創建自引用使用者表的簡化腳本:
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 )
即使我以某種方式對這些關係進行了循環,我也不明白為什麼不刪除行的直接子項將其 ManagerId 設置為
NULL
?我應該為這種情況創建一個觸發器來
NULL
為我設置 s 嗎?還是我明顯遺漏了什麼?
您可以在此處使用 INSTEAD OF 觸發器,或者在刪除使用者之前使用重新分配所有直接報告的邏輯。例如
--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