Delete cascade doesn't work in this scenario

Question

Scenario: Users can create a relationship type, like Friends, and then the user can link some other users as the relationship definition. if the user deleted, relationship definitions should be removed, and the relationships between users also need removed.

here is a demo database design on sql server: https://i.stack.imgur.com/3pcOy.png the foreign keys are:

Table 2.UserId reference Table 1.UserId;
Table 3.RelationShipId reference Table 2.RelationshipId;
Table 3.UserId reference Table 1.UserId;
Table 3.OtherUserId reference Table 1.UserId;

and my goal is when delete records from table 1, it will delete related records in table 2 and table 3; when delete records from table 2, it will delete related records in table 3.

and when i try to use "ON DELETE CASCADE", there is an error "may cause cycles or multiple cascade paths", also i don't want to use trigger.

do you have suggestion on this scenario? Thanks,

Can you explain the design please? I have a feeling the problem might be in the design itself — naomi, Apr 18 '13 at 11:23
Did you look for the solutions already answered here? [Try this one...][1] [1]: http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths — RGPT, Apr 18 '13 at 11:25
Whether you want to or not, in SQL server, you will have to use a trigger — StrayCatDBA, Apr 18 '13 at 11:37

score 0 · Answer 1 · answered Apr 18 '13 at 12:03

0

Isn't the userid in table 3 redundant? Or at least if you put the userid in table 3 why have table 2? The information in table 2 already exists in table 3.

Is there ever a situation where the user id on table 3 would not be the same as the relationship record on table 2? If not - table 2 is redundant.

answered Apr 18 '13 at 12:03

Jeff B

535
1
6
15

I have add the scenario in description. – FireFish Apr 18 '13 at 12:14

Delete cascade doesn't work in this scenario

1 Answers1