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.
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.
answered May 12 '10 at 02:12 PM
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.
answered May 12 '10 at 02:14 PM