更新时间:2022-12-09 16:41:52
不能有多个或循环的级联路径:它变得模棱两可你想做的事情(比如一个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