One of the questions that often comes up when working with SQL Server is how many foreign keys can be added to a single table. In this blog post, we will explore the limitations and changes in SQL Server versions regarding foreign key references.
In SQL Server 2014 and earlier versions, the maximum number of foreign key table references per table is 253. However, this limitation has changed in SQL Server 2016. To understand the limitations in SQL Server 2016, we need to grasp two important concepts:
- Outgoing Foreign Key References: This refers to a column in a table that refers to columns in other tables.
- Incoming Foreign Key References: This refers to a column in a table that is referenced by columns in other tables.
Let’s take a look at the following image to get a better understanding of these concepts:
Now that we have a clear understanding of these key terms, let’s answer the original question. In SQL Server 2016, a table can reference a maximum of 253 other tables and columns as foreign keys (Outgoing Foreign Key References). However, the limit for the number of other tables and columns that can reference columns in a single table (Incoming Foreign Key References) has been increased from 253 to 10,000.
It’s important to note that self-referencing foreign keys, where a table column references itself in the same table, are still limited to 253 in all versions of SQL Server.
According to the Books Online documentation, greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. However, MERGE operations are not supported. Additionally, tables with a foreign key reference to themselves are still limited to 253 foreign key references. It’s also worth mentioning that greater than 253 foreign key references are not currently available for column store indexes, memory-optimized tables, or Stretch Database.
Understanding the limitations and changes in foreign key references in SQL Server can help you design and optimize your database schema effectively. It’s important to consider these limitations when working with large databases or complex relationships between tables.
Stay tuned for more SQL Server tips and tricks in our upcoming blog posts!