|
Hi, I have User table with a column named USER_ID. Now all the other tables say 50 tables ,have created by and modified by audit fields. Is it a good idea to put a foreign key on the two audit fields in every table? How much perforamce hit/gain it cause? To add a little bit of my own research MSDN says Foreign key table references per table4 253 http://msdn.microsoft.com/en-us/library/ms143432.aspx Does it mean User table can be referenced only 253 times. thanks
(comments are locked)
|
|
Short answer - it depends. 254 is truly too many in any SQL 2005 database per table. The max specification that you quoted is how many foreign keys you can reference from one table out (i.e. 253 columns in your current table may be foreign keys to other tables). The reason this depends will be based on requirements, design, and usage. Too many foreign keys may be convoluted and too few may not offer up adequate integrity. I think putting an FK on those fields may be fine. Performance may be negligible - if indexed properly. I would defer back to the requirements concerning the audit fields. Nice one, refer back to the requirements, absolutely.
May 12 '10 at 03:18 PM
Grant Fritchey ♦♦
I have never yet seen a need for more than 3 in real life. But then as you say, it depends.
May 12 '10 at 08:02 PM
TimothyAWiseman
(comments are locked)
|
|
If you need to JOIN back to the User table and you care about data integrity then reference the foreign key. The 253 limit you refer to is how many keys a table can reference, not how many tables can reference a key. Concise and correct...
May 12 '10 at 04:22 PM
Matt Whitfield ♦♦
I wish I could mark this as answer too! thanks.
May 12 '10 at 05:02 PM
Bhaskar
No problem... glad we can help out.
May 12 '10 at 05:06 PM
Blackhawk-17
(comments are locked)
|
