Yesterday, I was attending a database design meeting. In the meeting, I was asked one of the questions – “How many foreign keys can you have on a single table?” Honestly, I had no clue about the limit. But, I must acknowledge that it was an excellent question to learn something new.

Once the meeting got over, I started testing to understand the limit of number foreign keys. I completely agree with your thought that I should have just googled it to get the answer, but I chose to go with a lengthy process to have a better understanding of it. So, the first things to know;

What is a Foreign Key (FK)?

A Foreign Key is a link between two tables that is used to enforce referential integrity in the RDBMS. When the FK constraint is added to an existing column or columns in the table, It must be validated by the RDBMS before accepting any kind of modification on the table. So, we can say that a foreign key ensures data integrity.

Real-Time Used Case to Understand the Foreign Key

Suppose, you have a list of students who are studying in the school “Dharmendra”. Now, the school decided to issue student card to only those students who are part of the list. If any student is not part of the list, he\she must not be issued a student card. If the students are part of the list, they can be given N number of cards. 

USE master;
GO
IF DATABASEPROPERTYEX ('Dharmendra', 'Version') > 0
DROP DATABASE Dharmendra;
GO
-- Let's create a database "Dharmendra" and it is going to act as the school
CREATE DATABASE Dharmendra;
GO
USE Dharmendra
GO
SET NOCOUNT ON
GO
/*
Now, create a table to register all the students. 
Each entry of the table is going to show a unique student.
*/CREATE TABLE dbo.studentlist
(
studentid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL
)
GO
-- Insert a student record into the table studentlist
INSERT INTO dbo.studentlist (firstname, lastname)
SELECT 'Sachit', 'Keshari'
GO
/*
Now, the school has decided to give student card. So, let's create another table "studentcard".
The reason is to create another table instead of the using the exisiting table studentlist - 
a registered student can be issued one or more studentscard, if they lost the card or the card got damanged. 
So, student entry will not be unique in the table studentcard. 
But, it will have unique entry for issued card.
*/CREATE TABLE dbo.studentcard
(
studentcardid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
studentid INT NOT NULL,
hoursstudy TINYINT NOT NULL,
presentday DATETIME NOT NULL
)
GO
/*
The school rule is - StudentCard will be only issued to registered student. 
Basically, the students who are part of studentlist table will only get the studentcard. 
So create a FOREIGN KEY on the table dbo.studentcard. 
Once you create the FOREIGN KEY on the table dbo.studentcard, SQL Server is going to validate 
each student entry with the table studentlist before issuing any student card.
So, If any student is not part of the table studentlist. He/she willn't be issued the card.
*/ALTER TABLE dbo.studentcard ADD CONSTRAINT FK_studentcard_studentid FOREIGN KEY (studentid) 
REFERENCES dbo.studentlist(studentid) ON DELETE CASCADE
GO
-- Let's issue a card to student 1
INSERT INTO dbo.studentcard (studentid, hoursstudy, presentday)
SELECT 1, 8, '2018-01-01'
GO
-- Check the entry details 
select * from dbo.studentlist
select * from dbo.studentcard

Here is the output of the above code;

Till now, you can issue a student card to student “1” who is part of the table studentlist. Let’s try to issue a student card to a student who is not part of the studentlist table.

-- Now, Issue a card to student who is not part of the table studentlist
INSERT INTO dbo.studentcard (studentid, hoursstudy, presentday)
SELECT 2, 8, '2008-01-01'
GO

You will end up with below error. It is because when Foreign Key validate the student “2” entry in the table studentlist, It doesn’t find any information in that. So, the validation failed with below error.

Msg 547, Level 16, State 0, Line 70
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_studentcard_studentid". The conflict occurred in database "Dharmendra", table "dbo.studentlist", column 'studentid'.
The statement has been terminated.

How many foreign keys can you have on a single table?

SQL Server 2014 and earlier versions, supports 253 as a maximum Foreign Key table references per table. However, this limitation changes from SQL Server 2016 onwards. If you follow the MSDN link, below is what it says;

A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 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:

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

Hope, you enjoyed exploring Foreign Key concept and limitation!

The post What is a Foreign Key & How many foreign keys can you have on a single table? appeared first on .