SQL SERVER | How many foreign keys can we have for a table?

Can you tell me how many foreign keys you can create on a single table?. Recently someone from my team asked me this. At that time I told him that SQL Server provides many he need not to worry. He then asked me – Do i know that number. Honestly at that time i was not aware of. I told him that i shall check and get back to him.

So i have gone through the Maximum Capacity Specifications for SQL Server and came up with a number.

Answer-

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Foreign key table references per table4	253	253

Foreign Key Constraints

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

Notes from Microsoft

A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:

1. Greater than 253 foreign key references are only supported for DELETE DML operations. UPDATE and MERGE operations are not supported.

2. A table with a foreign key reference to itself is still limited to 253 foreign key references.

3. Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, Stretch Database, or partitioned foreign key tables.

References

1	https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server
2	https://msdn.microsoft.com/en-IN/library/ms143432(v=sql.105).aspx
3	https://social.msdn.microsoft.com/Forums/sqlserver/en-US/701dde61-ad22-4923-b9ff-8e142c5b5af4/how-many-foreign-keys-can-we-have-for-a-table?forum=sqlgetstarted
4	https://docs.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail	URL
☛ SQL Advance Puzzles	https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries	https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles	https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles	https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers	https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com