Creating Two Cascading Foreign Keys Against Same Target Table/Col

Question

I have the following tables:

 user (userid int [pk], name varchar(50))
 action (actionid int [pk], description nvarchar(50))

being referenced by another table that captures the relationship:

<user1> <action>'s <user2>.

I did this with the following table:

userAction (userActionId int [pk], actionid int [fk: action.actionid], **userId1 int [fk ref's user.userid; on del/update cascade],  userId2 int [fk ref's user.userid; on del/update cascade]**).

However, when I try to save the userAction table i get an error because I have two cascading fk's against user.userid.

Is there any way to remedy this or must I use a trigger?

Answer 1

You could use a trigger, but I think it is better to actually write the deletes. Cascading deletes are often the casue of poor performance if a large set of records gets deleted and we do not allow them here. You can't base their use on the fact that the application only deletes one record at a time. What if that one record has thousands of child records? What if it has millions? Cascaded delete or deleting other tables through a trigger is extremely risky behavior. It is better to write the code the delete exactly what you want, perhaps even doing batch processing if there are too many child records. Further the fact that there are child records is often a reson why you shouldn't delete the parent record. For instance suppose client A is no longer an active client. But he has bought millions of dollars of stuff from you in the past. Do you really want to mess up the accounting records by deleting a million old orders? Maybe marking the record as inactive is a better choice.