SQL Server 2008-多个级联FK-我需要触发器吗? [英] SQL Server 2008 - Multiple Cascading FK's - Do i need a trigger?

问题描述

我在用户和发布之间存在1 .. *关系. (一个用户有很多帖子)

I have a 1..* relationship between User and Post. (one user has many posts)

帖子具有一个名为"UserId"的FK，该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的表.一条帖子有很多有用的帖子.

I have a table called PostHelpful. One Post has many Helpful's.

Helpful对用户具有级联FK(因此，删除用户时，其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".

因为如果我(当前)删除一个用户，它将删除他们的帮助.但是我也在尝试向发布中添加级联，这样做会删除发布，然后尝试删除该发布的有用内容(因为有用内容还有要层叠的FK).在那种情况下，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 .但是在执行此操作时，最好使用触发器删除不太独立的实体的记录.换句话说，帖子与 Users 和 PostHelpfuls 旁边的表相关的可能性大于 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.

这篇关于SQL Server 2008-多个级联FK-我需要触发器吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！