在具有两个 FK 到同一个表的表上级联删除

2022-01-20 00:00:00 foreign-keys sql-server cascade

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

I have a relation called Friends with the following columns,

User1ID   
User2ID
Since

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

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?

推荐答案

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

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)

我会使用存储过程首先在事务中从 Friends 中删除，然后从 Users 中删除(当然是在 TRY/CATCH 中处理错误)

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

相关文章