SQL Server Foreign Key: Everything you need to know

If you have been working with SQL Server for a while, you might have heard of something called referential integrity. This is basically the idea that references between tables are accurate.

In SQL Server, there are plenty of times where we’ll need to reference data from one table in another table. For example, think about a simple Orders table that keeps track of orders placed for a business. That Orders table will most likely reference Customer details and Product details, right? We obviously need to know who placed the order, and we obviously need to know what they bought!

Think about what a problem it would be if an order was placed for a product that doesn’t exist, or if an order was placed by a customer who doesn’t exist.

This is what we mean by referential integrity. In SQL Server, we want to do our best to prevent things like that from happening so that we maintain the integrity of our data.

One of the best ways to maintain referential integrity is to introduce SQL Server foreign key constraints to our tables. If done correctly, we can create a situation where it’s not possible to create invalid links between tables.

Do you know what a SQL Server foreign key constraint is? Do you need to learn a thing or two about referential integrity?

In this tutorial, we’re going to give you everything you need to know about foreign key links in SQL Server.

We will discuss these topics:

1. What is a SQL Server foreign key?
2. Understanding a referenced table and a referencing table
3. Syntax for creating a foreign key constraint
4. Understanding ON DELETE
5. Understanding ON UPDATE
6. Tips and tricks

Also, don’t forget to download your FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

Let’s get into it.

1. What is a SQL Server foreign key?

A SQL Server foreign key constraint establishes a relationship between two tables. The first table contains a primary key column (or unique key column), and is known as the referenced table (sometimes called the parent table). The second table is given the foreign key constraint on a column, and is called the referencing table (sometimes called the child table). The foreign key column can only contain values that exist in the primary key column of the referenced table.

A foreign key constraint creates a situation where invalid data physically cannot exist in our referencing table, therefore maintaining referential integrity.

2. Understanding a referenced table and a referencing table

To better understand foreign key links, let’s create a few simple tables and demonstrate why it would be good to create foreign key constraints on some of those tables.

Let’s first create a Customers table (utilizing the handy IDENTITY property) and add some data to it:

CREATE TABLE Customers
(
CustID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

INSERT INTO Customers (FirstName, LastName)
VALUES
('Charles', 'Green'),
('Michael', 'Green'),
('Bridgett', 'West')

Great, now let’s add a Products table (again, using the IDENTITY property with a seed value of 100, step value of 5) and add some data to it:

CREATE TABLE Products
(
ProdID INT IDENTITY(100,5) PRIMARY KEY,
ProductName VARCHAR(25),
Price DECIMAL(5,2)
)

INSERT INTO Products(ProductName, Price)
VALUES
('Coffee table', 198.00),
('Side tables', 119.00),
('Coat rack', 39.00)

Good. Now let’s think about what an Orders table would look like. This table, of course, contains details about orders placed for our products. An order ought to contain details about which customer placed the order, and what product they bought.

Therefore, we should have a CustID column and a ProdID column. We can use those columns to link back to the Customers and Products tables respectively to see who placed the order and what they bought.

Here is the definition of the table (without any foreign key constraints at the moment):

CREATE TABLE Orders
(
OrderID INT IDENTITY(800,2) PRIMARY KEY,
CustID INT,
ProductID INT,
OrderDate DATETIME
)

And here is the data we’ll add to it.

INSERT INTO Orders (CustID, ProductID, OrderDate)
VALUES
(2, 105, '9/1/2021'),
(1, 100, '9/2/2021')

Presently, this table can have invalid data entered into it

Let’s double check what’s in our Customers and Products tables:

Cool, now let’s take a look at the data in our Orders table:

Ok, so in looking at the data in the Orders table, I can infer some things. It looks like Order # 800 was a set of Side tables for Mr. Michael Green.

Also, Order # 802 was a Coffee Table for Mr. Charles Green.

Cool. It looks like valid data was entered into the Orders table…..so far….

However, you need to realize there is presently nothing stopping me from entering invalid data into the Orders table. Presently, I can create an order for a customer that doesn’t exist. Maybe that non-existent customer also purchases a non-existent product!

Here’s what I mean:

INSERT INTO Orders (CustID, ProductID, OrderDate)
VALUES
(88372, 9982872, '9/5/2021'),
(383876, 218873, '9/9/2021')

If I run that INSERT statement, then run a quick SELECT against the Orders table, we see it contains invalid data:

Folks, those customers and products don’t exist!

This table has lost it’s integrity. It is referring to data that doesn’t exist, which is exactly what we don’t want. Through the use SQL Server foreign key constraints, we could have stopped this from happening.

Let’s delete those two invalid Orders, then see if we can create some foreign key constraints:

DELETE FROM Orders WHERE OrderID IN (804,806)

3. Syntax for creating a foreign key constraint

Creating a SQL Server foreign key constraint is not too difficult.

Let’s think about the ProductID column of the Orders table. We want it to have a foreign key constraint to reference data in the ProdID column of the Products table. In other words, in the Orders table, we can only reference products that exist in the Products table.

That last paragraph contains everything we need to know to construct our foreign key constraint.

Here is the general syntax we’ll use, in an ALTER TABLE statement:

ALTER TABLE referencingTable
ADD CONSTRAINT constraintName FOREIGN KEY(referencingColumn)
REFERENCES referencedTable(columnToReference)

Let’s talk about the parts of this:

• constraintName: This is the name of the constraint. It can be anything you want it to be!
• referencingTable: The table we want to create the constraint for. In our case, the referencing table is the Orders table.
• referencedTable: The table whose primary key values we want to reference. In our case, the referenced table is the Products table
• referencingColumn: This is the column we want to actually add the foreign key constraint to. In our case, this is of course the ProductID column of the Orders table.
• columnToReference: This is the primary key (or unique key) column in the referenced table. In our case, it’s the ProdID column of the Products table. Remember, the idea of the constraint is to disallow a row from being inserted into the Orders table if it references a product that does not exist in the Products.ProdID column.

So if we want to name our constraint fk_ProdID, our ALTER TABLE statement will look like this:

ALTER TABLE Orders
ADD CONSTRAINT fk_ProdID FOREIGN KEY(ProductID)
REFERENCES Products(ProdID)

Nice.

Let’s also do a similar constraint on the CustID column in the Orders table. We want to disallow rows from being inserted if it references a customer that does not exist in the Customers.CustID column:

ALTER TABLE Orders
ADD CONSTRAINT fk_CustID FOREIGN KEY(CustID)
REFERENCES Customers(CustID)

Nice.

Entering invalid data is now impossible

Now folks, let’s try running one of those INSERT statements from earlier:

INSERT INTO Orders (CustID, ProductID, OrderDate) 
VALUES 
(88372, 9982872, '9/5/2021')

We get a proper error message:

SQL Server first recognizes that our CustID is invalid. The CustID of 88372 does not exist in the Customers.CustID column.

So let’s fix that part of it. We’ll make this order for Bridgette West, who has a CustID of 3. But let’s not change the ProdID value yet:

Good. Now we’re seeing an appropriate error message for the invalid ProdID we tried to use. There is no product in Products.ProdID with an ID of 9982872!

So once again, let’s put valid data this time. We’ll say the order was for a Coat Rack, which has a ProdID of 110:

Nice, the INSERT was finally successful. If we check the table, we see our new row is there!:

Nice! (your OrderID value will probably be different, that’s ok)

4. Understanding ON DELETE

We need to think about what might happen if the data in our referenced table (aka parent table) is deleted.

Let’s take that last insert we did into the Orders table. We created an order for Bridgette West (CustID 3), who ordered a Coat Rack (ProdID 110).

What would happen if we deleted the Coat Rack from our inventory? Well, suddenly the order would reference a product that doesn’t exist.

Remember folks, the whole idea of a foreign key constraint is to prevent that from happening. Therefore, the default action that SQL Server will take is to disallow you from doing that. By default, SQL Server will not let you delete a referenced row.

To demonstrate, let’s try deleting the Coat Rack from our inventory:

It can’t be done!

When creating a foreign key constraint, we have the option to specify the ON DELETE keywords. Instead of simply disallowing the deletion of a referenced row, for example, we can tell SQL Server to do something else instead. We can tell SQL Server to do one of three things to the referencing rows (aka child rows): CASCADE, SET DEFAULT or SET NULL.

We need to talk about each of these:

Example of ON DELETE CASCADE

It would be easiest to demonstrate how this works by simply showing some examples.

Let’s say we want to modify the foreign key constraint on the ProductID column of the Orders table. Instead of simply disallowing a referenced row from being deleted, what I want to happen is if a referenced row is deleted, I want to cascade that change to any referencing rows. In other words, not only do I want to delete a referenced row, but I also want to delete any referencing rows.

That’s exactly what ON DELETE CASCADE will do!

But how do we change the constraint so it will do that? Unfortunately, there isn’t a way to simply alter a constraint. What we’ll need to do is drop the constraint and re-create it with the new keywords. We can drop the constraint fairly easily:

ALTER TABLE Orders DROP CONSTRAINT fk_ProdID

Ok, once that’s done, we can run another ALTER TABLE statement to re-create the constraint, with the new details:

ALTER TABLE Orders 
ADD CONSTRAINT fk_ProdID FOREIGN KEY (ProductID)
REFERENCES Products (ProdID) ON DELETE CASCADE

You see the ON DELETE CASCADE keywords at the end? That’s it!

Let’s check the data in our Products and Orders table before we demonstrate the new behavior:

Let’s delete ProdID 110 now:

DELETE FROM Products where ProdID = 110

Now let’s check the data:

It worked! Not only did it delete our product, it also deleted any orders that reference that product. Perfect.

Let’s re-add the Product and the Order to demonstrate the other keywords:

INSERT INTO Products VALUES ('Coat Rack', 49.00)
INSERT INTO Orders VALUES (3, 115, '9/5/2021')

(In the insert into Orders, you’re ProductID ought to be 115 if you’ve been following along. If it isn’t, that’s fine. You’ll just use a different value. Use whatever ProdID was generated from the insert into the Products table.)

Here’s my data:

Example of ON DELETE SET NULL

Maybe you can guess what this set of keywords will do. When we delete a referenced row, any referencing rows will be given a NULL value in the referencing column.

(A column with a foreign key constraint can contain NULL. No problem.)

Let’s demonstrate.  It’s going to be a similar story for ON DELETE SET NULL. We need to delete the old constraint, and re-create it with the new details:

--Drop old constraint
ALTER TABLE Orders DROP CONSTRAINT fk_ProdID

-- Recreate constraint using ON DELETE SET NULL
ALTER TABLE Orders
ADD CONSTRAINT fk_ProdID FOREIGN KEY (ProductID)
REFERENCES Products(ProdID) ON DELETE SET NULL

Great, let’s double-check what’s in the tables presently:

So let’s delete ProdID 115:

Cool, now let’s check the data:

Nice! The product was successfully removed from the Products table, and the referencing row in the Orders table now contains NULL in the ProductID column! Perfect.

Again, let’s add the product back so that we can do more examples:

--Put the product back
INSERT INTO Products VALUES ('Coat Rack', 49.00)

-- Change the referencing row from NULL to the new ProdID of the Coat Rack. 
-- It should be 120 for you, but double check
UPDATE Orders SET ProductID = 120 where OrderID = 820

Example of ON DELETE SET DEFAULT

Last but not least, we have ON DELETE SET DEFAULT. This setting works if the referencing column has a default constraint. If a referenced row is deleted, the value in the referencing column will be set to the default value outlined in the default constraint.

Here’s the crazy part about this that NOBODY is talking about: The default value still needs to exist in the referenced table.

I have a full tutorial dedicated to this topic, make sure you check it out:

Do you know this IMPORTANT RULE about Foreign Key Constraints?

Here’s another interesting tip about ON DELETE SET DEFAULT:

If there is no default constraint on the column, and the column is nullable, NULL becomes the default value.

So essentially in that case, referencing rows would be given a value of NULL if their referenced row is deleted…..which is exactly what ON DELETE SET NULL would do.

In my opinion, the only way I would use ON DELETE SET DEFAULT is if I purposefully created a dummy row in my referenced table. Then, in the default constraint of the referencing table, I would specify the value of that dummy row.

To bring it back to our example, maybe I would create a row in our Products table with a ProdID of 0, and give it a ProductName of “N/A” and a Price of 0.00. Then in my default constraint in Orders.ProductID, it would be no problem to specify a default value of 0 because that ID actually exists in the Products table now.

From a business perspective, we would just need to understand that an order with a ProductID of 0 basically means the order has no actual product tied to it.

For now, for my foreign key constraints, I’m gonna stick with using either ON DELETE CASCADE or ON DELETE SET NULL.

5. Understanding ON UPDATE

The ON UPDATE settings for a foreign key constraint follows the same idea as the ON DELETE setting. If a parent row is updated, we make changes to any child rows that reference it.

ON UPDATE has the same three options that ON DELETE does: CASCADE, SET NULL and SET DEFAULT.

Setting up some data

Folks, I sort of shot myself in the foot.

The Primary Key columns of the Customers and Products tables both use the IDENTITY property. Once an IDENTITY value is given to a row, it can’t be updated.

So I can’t demonstrate any of the ON UPDATE settings using those tables. Let’s create two very simple tables called Offices and Employees:

CREATE TABLE Offices
(
OfficeID INT PRIMARY KEY,
OfficeName VARCHAR(20)
)

INSERT INTO Offices (OfficeID, OfficeName)
VALUES
(100, 'Buffalo'),
(150,'Orlando'),
(200,'Seattle')

CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
OfficeID INT CONSTRAINT fk_OfficeID FOREIGN KEY REFERENCES Offices(OfficeID)
)

INSERT INTO Employees (EmployeeID, FirstName, LastName, OfficeID)
VALUES 
(10, 'Doug','Brodie',200),
(15, 'Kathryn','Armsdale',100),
(20, 'Michael','Thatcher',150),
(25, 'George','Walker',150)

Each table still has a primary key column, but it doesn’t use the IDENTITY property. This means I am responsible for populating and maintaining that column.

Also, notice I specified the foreign key constraint in the table definition this time. The Employees.OfficeID column has a foreign key constraint tied to the Offices.OfficeID column.

Ok, now we can demonstrate how to use ON UPDATE.

Example of ON UPDATE CASCADE

Let’s check the data in our tables:

We want to change the constraint so that if we change a referenced OfficeID values in the Offices table, it changes any referencing OfficeID values in the Employees table too.

So we need to drop the existing foreign key constraint and re-create it with the ON UPDATE CASCADE setting:

--Drop the old constraint
ALTER TABLE Employees DROP CONSTRAINT fk_OfficeID

--Recreate the constraint with the ON UPDATE CASCADE setting specified
ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID) 
REFERENCES Offices(OfficeID) ON UPDATE CASCADE

Cool, let’s update the ID of the “Seattle” office in the Offices table. Let’s change it to 250:

Perfect! It updated the referencing row for us!

Example of ON UPDATE SET NULL

Let’s drop the old constraint and re-create it with ON UPDATE SET NULL specified:

--Drop the old constraint
ALTER TABLE Employees DROP CONSTRAINT fk_OfficeID

--Recreate the constraint with the ON UPDATE SET NULL setting specified
ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID) 
REFERENCES Offices(OfficeID) ON UPDATE SET NULL

Cool, now let’s run another update to change the ID of the “Orlando” office to 125. There are two referencing rows in the Employees table, so those rows should be given a value of NULL in their OfficeID column:

Perfect!

Example of ON UPDATE SET DEFAULT

This setting is just as strange as ON DELETE SET DEFAULT which we already talked about.

I want to demonstrate the idea of creating a dummy row in the referenced table. Then in the default constraint, we’ll say the default value is the ID of the dummy row.

Let’s create the dummy row in Offices:

INSERT INTO Offices (OfficeID, OfficeName) VALUES (0, 'N/A')

Ok, now let’s create a default constraint on the Employees.OfficeID column, specifying a default value of 0 (which, again, is the ID of the dummy row in the Offices table):

ALTER TABLE Employees ADD CONSTRAINT dflt_OfficeID DEFAULT 0 FOR OfficeID

Alright, now let’s drop the old foreign key constraint and re-create it with the ON UPDATE SET DEFAULT setting:

--Drop the old constraint
ALTER TABLE Employees DROP CONSTRAINT fk_OfficeID

--Recreate the constraint with the ON UPDATE SET DEFAULT setting specified
ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID) 
REFERENCES Offices(OfficeID) ON UPDATE SET DEFAULT

Ok, let’s remind you what’s in the two tables:

Let’s update the “Buffalo” office to have an ID of 20. Because of the default constraint (and because was have the foreign key constraint set to ON UPDATE SET DEFAULT), the child row in the Employees table should get it’s OfficeID value updated to 0:

Perfect!

Again folks, this only works because the value of 0 actually exists in the referenced table.

Learn more: Do you know this IMPORTANT RULE about Foreign Key Constraints?

Also, the same factoid is true about ON UPDATE SET DEFAULT: If there isn’t a default constraint, and the column is nullable, NULL becomes the default value.

In that case, again, you might as well just use ON UPDATE SET NULL.

6. Tips and tricks

Here is a list of helpful tips and tricks you should know about SQL Server foreign key constraints:

Tip #1: ON DELETE NO ACTION and ON UPDATE NO ACTION are the default settings with a foreign key constraint

There is actually a fourth option you can specify with your ON DELETE or ON UPDATE settings: NO ACTION.

If this setting is put in place, you simply cannot delete or update the primary key value of a referenced row if referencing rows exist in the referencing table.

This is the default setting if you don’t specify anything for ON DELETE or ON UPDATE when you create the foreign key constraint. Like I did here:

CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
OfficeID INT CONSTRAINT fk_OfficeID FOREIGN KEY REFERENCES Offices(OfficeID)
)

I created a foreign key constraint, but I didn’t specify anything about ON DELETE or ON UPDATE. In that case, both setting are given the NO ACTION option.

Tip #2: You can specify both an ON DELETE and an ON UPDATE setting

Folks, you can mix and match these settings all day long. You can create your foreign key constraint in many different ways, such as:

• ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID) 
  REFERENCES Offices(OfficeID) ON DELETE NO ACTION ON UPDATE SET NULL

• ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID)
  REFERENCES Offices(OfficeID) ON DELETE SET NULL ON UPDATE SET DEFAULT

• ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID)
  REFERENCES Offices(OfficeID) ON DELETE SET NULL ON UPDATE SET NULL

• ALTER TABLE Employees ADD CONSTRAINT fk_OfficeID FOREIGN KEY (OfficeID)
  REFERENCES Offices(OfficeID) ON DELETE CASCADE ON UPDATE SET NULL

Whatever you want!

Tip #3: You can’t delete a referenced table, even if the referencing table doesn’t contain any data

If you create a foreign key link between two tables, you can’t delete the parent table as long as the foreign key constraint exists. This is true even if the referencing table (aka child table) doesn’t contain any data.

Take for example the Offices and Employees tables. As long at the fk_OfficeID constraint exists, I can’t drop the Offices table:

If I want to drop the Offices table, I need to first drop the foreign key constraint:

The same thing is true for the TRUNCATE TABLE statement. We can’t truncate the Offices table as long as the foreign key constraint exists. We would need to drop the constraint first if we wanted to truncate the Offices table.

Tip #4: You can see what constraints exist in a table by looking at the details of the table in the object explorer

You can see what constraints exist for a table by looking at the details of the table in the Object Explorer of SQL Server Management Studio. The foreign key constraints would be under the Keys folder.

For example, here’s the details for our Orders table:

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you…

Download your FREE SQL Server Constraints Ebook!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

This tutorial assumes you know a thing or two about primary key constraints. If you need a crash course on SQL Server Primary keys, check out this tutorial:

SQL Server Primary Key: How it works and 6 rules you need to know

Also, do you understand the difference between a primary key constraint and a unique key constraint? If not, check out this tutorial:

SQL Server Unique Index: Everything you need to know

After reviewing those tutorials, you will be a SQL Server KEY MASTER!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!