Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any maximum on the number of times a single table can be referenced in a SQL Server database via foreign key?

IE

Table A A_ID

Table B ~ B_ID, A_ID  
Table C ~ C_ID, A_ID  
Table D ~ D_ID, A_ID  
...
share|improve this question

migrated from stackoverflow.com Jan 29 '12 at 6:50

This question came from our site for professional and enthusiast programmers.

2  
Making your example a bit clearer. –  Nicholas Carey Jan 27 '12 at 17:40
add comment

3 Answers

up vote 3 down vote accepted

The recommended limit for the number of foreign keys in a table is 253. See Maximum Capacity Specifications for SQL Server on MSDN.

Microsoft says:

"Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process."


EDIT:

Note that these limits apply to the detail table, the table containing the foreign keys. You add foreign key constraints to the detail table.

ALTER TABLE Detail 
ADD FOREIGN KEY (fk_main_id) REFERENCES Main (pk_main_id);

The referenced (main) table is not affected by this, therefore there are no limits on how many times the main table can be referenced.

share|improve this answer
    
Your link points to "Foreign key table references per table" and not "Foreign key table references to the same table" –  ypercube Jan 29 '12 at 16:05
    
You are right, I edited my post in order to clarify. –  Olivier Jacot-Descombes Jan 29 '12 at 16:34
add comment

The short answer is, "No." The longer answer is that there is no explicit restriction, but since those relationships require metadata, the do consume memory and resources: if you have, say, 10,000 foreign key relationships to a single table's primary key, you're likely to find that a delete against the referenced table might be a little...painful.

Here's what the documentation has to say:

Number of FOREIGN KEY Constraints In A Table

SQL Server does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain (which reference other tables), or the number of FOREIGN KEY constraints owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints is limited by your hardware configuration and by the design of your database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints. Consider the cost of enforcing FOREIGN KEY constraints when you design your database and applications.

It should also be noted, though, that, SQL Server's system views (e.g., sys.objects) use 32-bit integers as identifiers, the domain of which puts an implicit upper limit on the total number of foreign keys the database could contain.

Not that you'd ever come close to exceeding that limit.

share|improve this answer
add comment

As far as i know the maximum number of references is 253. I can hardly imagine a case where you have to go even near that mark.

share|improve this answer
    
we created row level security on our db so every row on every table ties back to 2 primary tables. –  Chuck Jan 27 '12 at 17:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.