on cascade delete on a table with two FK to the sa

问题:

This question already has an answer here:

MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table? (1 answer)

Closed 6 years ago.

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?

回答1:

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