Friday, March 23, 2012

Multiple Foreign Keys

What are the possible issues I could run in to having multiple foreign keys in a table. Here is why I ask. I have a db (sql server) that has a participant table, a forum table, and a forum reply table. Every record in the forum reply table is associated with the forum table via a PK-FK relationship w/cascading updates/deletes. The participants who post in these tables are not tied back to the participant table via a PK-FK relationship w/cascading updates/deletes. Should they be?

The problem I ran in to is that one particpant was deleted from the participant table but a post with their partid still existed in the forum or forum reply tables.

My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right? If I am right, what do I have to be careful of if I do that?

Let me know! Thanks!!My feeling is that anytime a participant is deleted, everything that pertains to them should go too, right?

I'd suggest you don't even delete the participant much less the replies. Forum threads would be rendered useless if you start deleting replies. Why not just add a flag to the participant table to indicate active/inactive status?

No comments:

Post a Comment