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

Bhaskar gravatar image

333 16 17 20

(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

CirqueDeSQLeil gravatar image

4.8k 10 11 15

Nice one, refer back to the requirements, absolutely.
May 12, 2010 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, 2010 at 08:02 PM TimothyAWiseman
(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

Blackhawk-17 gravatar image

11.9k 28 31 37

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 3148 times

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