How Many Foreign Key Can You Have on A Single Table?

Answer: SQL Server 2014 and earlier versions, supports 253 as a maximum foreign key table references per table. However, this limitation, changes in SQL Server 2016.

To understand the limitations in SQL Server 2016 we need to understand two important concepts

a) Outgoing Foreign Key References – A column in a table referring other table columns.

b) Incoming Foreign Key References – A column in a table which is referenced by other table columns.

Now we know the answer of two important key words, let us see the answer to our original question.

A table can reference a maximum of 253 other tables and columns as foreign keys (Outgoing Foreign Key References). SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (Incoming Foreign Key References), from 253 to 10,000. However, self referencing FK (where table column is referencing itself in the same table) still is limited to 253 in all the versions of SQL Server.

Here is limitation from Books On Line-

  • Greater than 253 FK references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
  • A table with a FK  reference to itself is still limited to 253 foreign key references.
  • Greater than 253 FK  references are not currently available for column store indexes, memory-optimized tables, or Stretch Database.
Advertisements

Author: tdmsql

I am a PL-Sql Developer. I am searching some sites sql studies but its bored. i like create studies note my own way. so that i created this site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s