在两个FK的表上级联删除到同一个表 [英] on cascade delete on a table with two FK to the same table

问题描述

我有一个名为 Friends 的关系，包含以下列：

  User1ID 
 User2ID 
由于
  

User1ID 和 User2ID 是关系中的一组主键。它们也是引用表的用户的外键。现在我想要添加一个 ON CASCADE DELETE ，这样当表Users的用户被删除时，表 Friends 也被删除。但是，MS SQL Server不允许我添加该约束。

任何想法，关于如何修改表，以完成任务？

解决方案

不能有多个或循环的级联路径：它变得模棱两可你想做的事情（比如一个CASCADE NULL和另一个CASCADE DELETE）

我会使用一个存储过程先从 Friends 中删除​​，然后从用户（在TRY / CATCH中当然要处理错误）

  BEGIN TRAN 
删除朋友WHERE User1ID = @UserID; 
删除朋友WHERE User2ID = @UserID; 
删除用户WHERE UserID = @UserID; 
 COMMIT TRAN 
  

I have a relation called Friends with the following columns,

User1ID   
User2ID
Since

User1ID and User2ID are a set of primary keys in the relation. They are also foreign keys referencing to the table Users. Now i want to add an ON CASCADE DELETE, such that when a user from table Users is deleted then the corresponding row from table Friends is deleted as well. However, MS SQL Server does not allow me to add that constraint.

Any ideas, about how can i modify the table, in order to accomplish that task?

解决方案

You can't have multiple or circular cascade paths: it becomes ambiguous what you want to do (say one CASCADE NULL and the other CASCADE DELETE)

I'd use a stored procedure to delete from Friends first in a transaction then from Users (in a TRY/CATCH of course to deal with errors)

BEGIN TRAN
   DELETE Friends WHERE User1ID = @UserID;
   DELETE Friends WHERE User2ID = @UserID;
   DELETE Users WHERE UserID = @UserID;
COMMIT TRAN

这篇关于在两个FK的表上级联删除到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！