x

Foreign key question: How many are too many

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

more ▼

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

Bhaskar gravatar image

Bhaskar
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

CirqueDeSQLeil
4.1k 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

Blackhawk-17
11.9k 28 31 36

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x107
x35

asked: May 12, 2010 at 01:43 PM

Seen: 3033 times

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