Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bobba Buoy
Starting Member
36 Posts |
 Posted - 2004-06-25 : 10:16:45
|
| 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!! |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-06-25 : 13:22:25
|
| Why not have a column in the participant table that indicates the status of the participant ie - Active or Disabled(Rather than deleted)?This could perhaps prevent a few unforseen problems.Duane. |
 |
|
|
Bobba Buoy
Starting Member
36 Posts |
Posted - 2004-06-25 : 17:10:21
|
| Actually, I have that option-an archive field. I am more concerned with the theoru behind multiple FKs. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-06-25 : 18:24:41
|
| Having more than one foreign key is not a problem. You should include every relationship that you want enforced.Personally, I don't care for cascading deletes, because it's so easy to wipe out way more data than you intended to by mistake. I prefer to structure any DELETE statements in the proper order so that they don't violate my constraints, and if I accidentally forget one, the system will abort the transaction and let me know I didn't do it right. It's just a nice safety feature I like.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
|
|
Multiple Foreign Keys