SQL Server 2008 - 多个级联 FK - 我需要触发器吗?
我在 User 和 Post 之间有 1..* 关系.(一个用户有很多帖子)
I have a 1..* relationship between User and Post. (one user has many posts)
Post 有一个名为UserId"的 FK，它映射到 User 表上的UserId"字段.
Post has a FK called "UserId", which maps to the "UserId" field on User table.
我尝试将此 FK 设置为 Cascade UPDATE/DELETE，但出现此错误:
I tried to set this FK as Cascade UPDATE/DELETE, but i get this error:
用户"表保存成功帖子"表- 无法创建关系FK_Posts_Users".
在表 'Posts' 上引入 FOREIGN KEY 约束 'FK_Posts_Users' 可能会导致循环或多个级联路径.指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束.无法创建约束.查看以前的错误.
'Users' table saved successfully 'Posts' table - Unable to create relationship 'FK_Posts_Users'.
Introducing FOREIGN KEY constraint 'FK_Posts_Users' on table 'Posts' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.
我有一个名为 PostHelpful 的表格.一个帖子有很多 Helpful.
I have a table called PostHelpful. One Post has many Helpful's.
Helpful 对 User 有一个级联的 FK(所以当一个 User 被删除时，他们的 Helpful 也会被删除).
Helpful has a cascading FK to User (so when a User is deleted, their Helpful's are also deleted).
但我认为这是抱怨多级联路径"的原因.
But i think this is the cause of complaint for "multiple cascade paths".
因为如果我(当前)删除用户，它将删除他们的帮助.但是我也试图向 Post 添加一个级联，这样做会删除 Post，然后尝试删除该 Post 的 Helpful(因为 Helpful 也有一个级联的 FK 到 Post).在那种情况下，SQL 会选择哪个级联 FK?
Because if i delete a User (currently), it will delete their helpfuls. But im trying to add a cacade to Post also, do it would delete the Post, then try and delete the Helpful's for that Post (as Helpful also has a cascading FK to Post). In that scenario, which cascading FK would SQL choose?
下面是这三个表的数据库图:
Here is the database diagram of the three tables in question:
如您所见，PostHelpful"是Post"和User"的子项(对两者都有 FK).
As you can see, "PostHelpful" is a child to both "Post" and "User" (has FK's to both).
所以我不能让两个键级联?我是否需要用户"上的触发器(删除后)来手动删除帮助信息(以及引用用户的其他表).
So i can't make both keys cascading? Do i need a trigger on "Users" (AFTER DELETE) to manually delete the helpfuls (and other tables referencing User).
推荐答案
SQL Server 选择哪条路径无关紧要，它不允许这样做，以免陷入妥协的境地.当我们遇到这种情况时，我们不得不求助于触发器.
It's not a matter of which path will SQL Server choose, it does not allow it so that it won't wind up in a compromising position. When we ran into this situation, we had to resort to a trigger.
1) 如错误消息所述，将 Users_PostHelpfuls FK 更改为 ON DELETE NO ACTION.
1) As the error message stated, change the Users_PostHelpfuls FK to ON DELETE NO ACTION.
2) 向用户添加 INSTEAD OF DELETE 触发器:
2) Add an INSTEAD OF DELETE trigger to Users:
CREATE TRIGGER dbo.Users_IO_Delete
ON dbo.Users
INSTEAD OF DELETE
AS
BEGIN;
DELETE FROM dbo.PostHelpfuls WHERE UserId IN (SELECT UserId FROM deleted);
DELETE FROM dbo.Users WHERE UserId IN (SELECT UserId FROM deleted);
END;
现在，FK 仍将强制执行 DRI，但触发器是级联删除而不是 FK 约束.
Now, the FK will still enforce DRI, but the trigger is cascading the delete rather than the FK constraint.
您可以在上述步骤中将 PostHelpfuls 替换为 Posts.但是这样做时最好使用触发器来删除不太独立的实体的记录.换句话说，与 PostHelpfuls 与 Users 和 PostHelpfuls 旁边的表相关的可能性更高用户和帖子旁边的表格.
You could replace PostHelpfuls with Posts in the above steps. But when doing this it's best to use the trigger to remove the less independent entity's records. In other words, it's more likely that Posts are related to tables beside Users and PostHelpfuls than PostHelpfuls is related to tables beside Users and Posts.
相关文章