Is there any maximum on the number of times a single table can be referenced in a SQL Server database via foreign key?
Table A A_ID Table B ~ B_ID, A_ID Table C ~ C_ID, A_ID Table D ~ D_ID, A_ID ...
migrated from stackoverflow.com Jan 29 '12 at 6:50
This question came from our site for professional and enthusiast programmers.
The recommended limit for the number of foreign keys in a table is 253. See Maximum Capacity Specifications for SQL Server on MSDN.
"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."
Note that these limits apply to the detail table, the table containing the foreign keys. You add foreign key constraints to the detail table.
The referenced (main) table is not affected by this, therefore there are no limits on how many times the main table can be referenced.
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
Here's what the documentation has to say:
It should also be noted, though, that, SQL Server's system views (e.g.,
Not that you'd ever come close to exceeding that limit.
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.