What is a Foreign Key & How many foreign keys can you have on a single table?

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!

2 thoughts on “What is a Foreign Key & How many foreign keys can you have on a single table?

  • Joe Celko

    >> 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. <<

    No, it is not a link. The term "link" refers to pointer chains (simple links, double links, etc.) which is one possible physical implementation. It is not the only physical implementation; you could do it with hashing or even brute force. The concept of a reference is fundamental to RDBMS, and has nothing to do with linkage.

    Your sample tables violate ISO 1179 naming conventions and you actually used the identity table property as a key! Never never do that! What does the physical insertion count to a physical storage on one disk have to do with the relational model? List is a term that refers to a physical storage model, like array, and therefore should never be part of a data element name. A data element names you tell us what it is and not how it's stored. I'm also a little curious about insanely long names (international postal standard for address line is only 35 characters), but I'm sure you did. Careful research and found somebody with a 50 character first name.

    CREATE TABLE Students
    (student_id CHAR(1)) NOT NULL PRIMARY KEY,
    student_firstname VARCHAR(50) NOT NULL,
    student_lastname VARCHAR(50) NOT NULL);

    — Insert a student record into the table Student

    In physical storage. We insert records; in SQL. We insert rows which are not anything alike. Why are you still using the original Sybase syntax instead of the values clause which has been standard SQL for over a decade?In short, you're missing all the basic terms and concepts

    INSERT INTO Students
    VALUES ('1234567890', 'Sachit', 'Keshari');
    /*
    Now, the school has decided to give student card. So, let's create another table "Student_Cards".
    The reason is to create another table instead of the using the exisiting table Student –
    a registered student can be issued one or more student card, if they lost the card or the card got damanged.
    So, student entry will not be unique in the table student card.
    But, it will have unique entry for issued card.
    */
    CREATE TABLE Student_Cards
    (student card_nbr CHAR(5) NOT NULL PRIMARY KEY,
    student_id CHAR(10) NOT NULL
    REFERENCES Students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    study_hours_cnt TINYINT NOT NULL,
    something_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL
    );

    You failed to explain the term DRI (declarative referential integrity) in the options that come with it; you only show deletion not updates. You failed to explain self referencing the table. . I'm sorry this is not a good article because the basic terminology being messed up, lack of ISO standards, and incomplete information.

    Reply
  • Dharmendra

    Thanks Joe for your great feedback! I wanted to write a small post on the foreign key but agreed it doesn’t cover everything here. But, I would be posting an updated blog on this with the entire scenario…

    Once again thank you for bringing it to my attention!

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *