SQL Server 2008 - Multiple Cascading FK's - Do i need a trigger?

Question

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

Post has a FK called "UserId", which maps to the "UserId" field on User table.

I tried to set this FK as Cascade UPDATE/DELETE, but i get this error:

'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.

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

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".

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:

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).

Answer 1

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) As the error message stated, change the Users_PostHelpfuls FK to ON DELETE NO ACTION.

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;

Now, the FK will still enforce DRI, but the trigger is cascading the delete rather than the FK constraint.

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.