Foreign key question: How many are too many


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.


more ▼

asked May 12, 2010 at 01:43 PM in Default

avatar image

333 17 19 24

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 12, 2010 at 02:12 PM

avatar image

5.5k 11 13 20

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 12, 2010 at 02:14 PM

avatar image

12.1k 30 36 42

Concise and correct...

May 12, 2010 at 04:22 PM Matt Whitfield ♦♦

I wish I could mark this as answer too! thanks.

May 12, 2010 at 05:02 PM Bhaskar

No problem... glad we can help out.

May 12, 2010 at 05:06 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 12, 2010 at 01:43 PM

Seen: 3927 times

Last Updated: May 12, 2010 at 05:57 PM

Copyright 2018 Redgate Software. Privacy Policy