sql server - 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

The approved answer is not a good answer. The scenario described is not bad design, nor is it "risky" to rely on the database to do its job.

The original question describes a perfectly valid scenario, and the design is well thought-out. Clearly, deleting a user should delete both the user's items (and any votes on them), and delete the user's votes on any item (even items belonging to other users). It is reasonable to ask the database to perform this cascading delete when the user record is deleted.

The problem is that SQL Server can't handle it. Its implementation of cascading deletes is deficient.