cascading deletes causing multiple cascade paths

Question

I am using SQlServer 2008, and an extract of some datatables is displayed below:

Users

Id (PK)

UserItems

UserId (PK) ItemId (PK) - (Compound key of 2 columns) ...

UserItemVotes

UserId (PK) ItemId (PK) VoterId (PK) - (Compound key of 3 columns)

I have the following relationships defined:

• User.Id -> UserItems.UserId
• (UserItems.UserId, UserItems.ItemId) -> (UserItemVotes.UserId, UserItemVotes.ItemId)
• UserId.Id -> UserItemVotes.VoterId

Now, I am having a problem when turning on cascading deletes. When adding the 3rd relationship I receive the error "...may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." I do not really want to do this, ideally if a user is deleted I would like to remove their useritem and/or their votes.

Is this a bad design? Or is there a way to get behaviour I want from SQL Server?

Answer 1

I would lead toward bad design. While most DBMSs can manage cascading deletes, it is risky to use this built in functionality. Your scenario is a perfect example of why these types of things are often managed in application code. There you can determine exactly what needs to be deleted and in what order.

Answer 2

"UserItems.ItemId -> UserItemVotes.UserId"

This one seems extremely suspect.