469,568 Members | 1,394 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

Number of foreign keys

I understand the number of foreign keys allowed is restricted by the DBMS I
use.
In a general relational schema design perspective how many foreign keys a
table shall have?
If I have large number of foreign keys what anamolies it will lead to?
Is this crucial to identify all the foriegn key relationships for a table?

Vinodh
Jul 20 '05 #1
1 2785
On Thu, 2 Dec 2004 13:06:57 +0530, Vinodh Kumar P wrote:
I understand the number of foreign keys allowed is restricted by the DBMS I
use.
Hi Vinodh,

For SQL Server 2000 (and 7.0), this limit is 253 foreign key table
references per table. I can't imagine any serious application ever
exceeding this limit.

In a general relational schema design perspective how many foreign keys a
table shall have?
Depends. Some tables will have only one or none, other tables might have
more (sometimes evemn much more) foreign keys. There is no general answer,
just as there is no single "one-size-fits-all" database design. That's why
requirements analysis and normalization are the most important stteps in
database design.

If I have large number of foreign keys what anamolies it will lead to?
None - fooreign keys PREVENT anomalies, as the database will check all
changes to the data to make sure the constraint is not violated. This will
slow down the processing of insert, update and delete statements, though
not by much (if you use proper indexes).

Is this crucial to identify all the foriegn key relationships for a table?


Yes. Unless you like cleaning up corrupted data. <g>

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Olivier Crèvecoeur | last post: by
10 posts views Thread by Bodza Bodza | last post: by
2 posts views Thread by Ian Davies | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.