Create Table SQL Server with Foreign Key Constraints

By: Jan Potgieter | Updated: 2022-08-09 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | > Database Design

Problem

A database is a store of information and often data is related to each other. When creating a database and tables to store information, linking different pieces together just makes sense. In this article, we will look at how to create SQL Server tables and use foreign keys to reference the related data in the different tables.

Solution

In this SQL tutorial, you will learn how to create a SQL Server database that holds information that relates to each other. For demonstration, we will create an HR database with two tables that store information about Companies and Employees.

We will work through the following:

Create two tables with a link that will not work.
Create two tables with a link that will work.
Add a foreign key to enforce referential integrity between the two tables.
Delete data from the tables.
Update and remove data to show how the foreign key protects the data.
Use the cascade option of the foreign key.

Setup the Scenario

First, below are the factors associated with this scenario:

Companies table: List of Companies
Employees table: List of Employees working at the Company
Rule: Employees can only work at one Company, and a Company can employ multiple Employees.

Let us set up the environment where we will be working. Open SQL Server Management Studio (SSMS), open a query window and run the below SQL commands to create the database we will be using. Note: when setting up a production database, remember to complete proper planning, i.e., creating the datafile and the logfile on separate disks and not on the C:\ drive.

For this SQL tutorial, we will create a SQL database with SQL Server's default settings.

--Create the HRDatabase
USE master
GO
 
DROP DATABASE IF EXISTS HRDatabase
GO
 
CREATE DATABASE HRDatabase
GO
 
USE HRDatabase
GO

Create Two New Tables with a Link that Will Not Work

Let us create a Companies table, an Employees table and insert some sample data with the following syntax:

--1st Try: Create a Companies and an Employees table and link them together
DROP TABLE IF EXISTS Companies;
GO
DROP TABLE IF EXISTS Employees;
GO

-- SQL CREATE TABLE Statement
CREATE TABLE Companies (
   ID            INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY,
   CompanyName   VARCHAR(80) NOT NULL, -- column name, data types and null value
   CompAddress   VARCHAR(80) NOT NULL,
   CompContactNo VARCHAR(20) NOT NULL,
   EmpID         INT         NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Companies_CreateDate DEFAULT getdate()
)
 
CREATE TABLE Employees (
   ID            INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY,
   EmployeeName  VARCHAR(80) NOT NULL,
   ContactNo     VARCHAR(20) NOT NULL,
   Email         VARCHAR(80) NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Employees_CreateDate DEFAULT getdate()
)
 
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo, EmpID) VALUES
('Alpha Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987' , 1),
('Bravo Company', '456 South Street, Brooklyn, Pretoria', '091 523 4789' , 1),
('Charlie Company' , '987 West Street, Lynnwood, Pretoria', '091 523 1235' , 1),
('Delta Company', '258 East Street,  The Meadows, Pretoria', '091 523 7414' , 1),
('Echo Company', '100 Amber Street, Hatfield, Pretoria', '091 523 9685' , 1)
 
INSERT INTO Employees (EmployeeName, ContactNo, Email) VALUES
('Joe Blogs' , '012 365 4789', '[email protected]')   ,
('Jane Doe' , '012 365 4789', '[email protected]')   ,
('John Smit' , '012 365 4789', '[email protected]')   ,
('Eddy Jones' , '012 365 4789', '[email protected]'),
('Steve Dobson', '012 365 4789', '[email protected]')
 
SELECT * FROM Companies
SELECT * FROM Employees

An EmpID column was added to the Companies table, which links it to the Employees table ID column.

When you look at the result set below, clearly something is wrong! That is, one employee is working for five different companies! That is impossible, according to the scenario for the database above.

Rule: Employees can only work at one Company, and a Company can employ multiple Employees.

There is a one-to-many relationship between the two tables; one employee can work at many companies, which does not follow the rule for this scenario. Therefore this is not the correct way to set this up.

Create Two New Tables with a Link that Will Work

Let us create the Companies and Employees tables again but change the link between them with the following syntax:

--2nd Try: Create a Companies and an Employees table and link them together
DROP TABLE IF EXISTS Companies;
GO
DROP TABLE IF EXISTS Employees;
GO

-- SQL CREATE TABLE Statement
CREATE TABLE Companies (
   ID            INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY, -- Primary Key Constraint
   CompanyName   VARCHAR(80) NOT NULL, -- column definition
   CompAddress   VARCHAR(80) NOT NULL,
   CompContactNo VARCHAR(20) NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Companies_CreateDate DEFAULT getdate()
)
 
CREATE TABLE Employees (
   ID            INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY,
   EmployeeName  VARCHAR(80) NOT NULL,
   ContactNo     VARCHAR(20) NOT NULL,
   Email         VARCHAR(80) NOT NULL,
   CompID        INT         NOT NULL,
   CreateDate    DATETIME NOT NULL constraint DF_Employees_CreateDate DEFAULT getdate()
)
 
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) VALUES
('Alpha Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987' ),
('Bravo Company', '456 South Street, Brooklyn, Pretoria' '091 523 4789' ),
('Charlie Company', '987 West Street, Lynnwood, Pretoria', '091 523 1235' ),
('Delta Company', '258 East Street, The Meadows, Pretoria', '091 523 7414' ),
('Echo Company', '100 Amber Street, Hatfield, Pretoria', '091 523 9685' )
 
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID) VALUES
('Joe Blogs', '012 365 4789', '[email protected]', 1),
('Jane Doe', '012 365 4789', '[email protected]', 2),
('John Smit' , '012 365 4789', '[email protected]', 1),
('Eddy Jones' , '012 365 4789', '[email protected]'   , 4),
('Steve Dobson' '012 365 4789', '[email protected]', 5)
 
SELECT * FROM Companies
SELECT * FROM Employees

This time we added a column in the Employees table, CompID, which links to the ID column in the Companies table.

The result set makes more sense now since a single company can have more than one employee. That is in line with the scenario we set out for this demonstration.

See the result set and the SQL diagram for the two tables below.

This will work for our scenario! Multiple employees can work at the same company.

Again, it means that there exists a one-to-many relationship between the two tables, but this time one employee can work at only one company, which is in line with the rules for the scenario.

Add a Foreign Key to Enforce Referential Integrity Between Two Tables

Even though we have created the existing tables correctly, there is still no link (or foreign key) that binds the two tables. Now, let's enforce the integrity of the data by adding a foreign key.

A FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table.

See some good websites explaining more about foreign keys:

With the definition of the tables as created in the above section, we will make the following change to add a foreign key constraint and add a new employee.

--Now, let's enforce the integrity of the data by adding foreign keys
ALTER TABLE dbo.Employees ADD CONSTRAINT
   FK_Employees_Companies FOREIGN KEY (CompID) REFERENCES dbo.Companies (ID)
 
--Let's add some Employees that works at an unlisted company with an ID = 9
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID) VALUES
('Joe Blogs' , '012 365 4789', '[email protected]'   , 6)

We get this error, because the company does not exist. The constraint has enforced data integrity by preventing the addition of an employee that works at a company that does not exist!

--Error:
--Msg 547, Level 16, State 0, Line 151
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_Companies".
--The conflict occurred in database "HRDatabase", table "dbo.Companies", column 'ID'.
--The statement has been terminated.

We will add the company and try the employee insert again.

--Let's add the Company
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) VALUES
('Foxtrot Company' , '123 North Street, Garsfontein, Pretoria', '091 523 6987' )
 
SELECT * FROM Companies
 
--Now, try again to add the Employee that works for ID = 6, that is Foxtrot Company
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID) VALUES
('Joe Blogs' , '012 365 4789', '[email protected]' , 6)
 
SELECT * FROM Companies
SELECT * FROM Employees

This time it was successful.

In the image below we can see how the two tables are related. The key on the relationship line means one, and the infinity sign on the other side means many. Hence the one-to-many relationship between the tables.

Delete Data from Tables

Let's try to delete the new company we just created that has one employee associated with it and see what happens.

--Now what will happen if you remove a Company where an Employee works?
DELETE FROM Companies where ID = 6

We get this error and cannot remove a Company that has an Employee.

--Error:
--Msg 547, Level 16, State 0, Line 184
--The DELETE statement conflicted with the REFERENCE constraint "FK_Employees_Companies".
--The conflict occurred in database "HRDatabase", table "dbo.Employees", column 'CompID'.
--The statement has been terminated.

If we remove the one Employee that works for Company 6 first, then we can remove the Company as well.

DELETE FROM Employees where ID = 7
 
--Now try to remove the Company

DELETE FROM Companies where ID = 6
 
SELECT * FROM Companies
SELECT * FROM Employees

As you can see, when a foreign key is setup between two tables, the delete must be handled in the correct order.

Update Data to Show How the Foreign Key Protects Data

When updates are performed, like changing the CompID in the Employees table, it works as shown below.

--What will happen when Joe Blogs resigns from 'Alpha Company' and starts working at 'Echo Company'?
UPDATE Employees SET CompID = 5 WHERE ID = 1
 
SELECT * FROM Companies
SELECT * FROM Employees
 
--No Problem!
--What will happen if Eddy Jones resigns and joins Charlie Company?
 
UPDATE Employees SET CompID = 3 WHERE ID = 4
 
SELECT * FROM Companies
SELECT * FROM Employees

Again, when the foreign keys are set up correctly and updates to records are performed correctly and within the rules, all is working fine.

Cascade Option of the Foreign Key

To finish this tutorial, let us look at the cascade option of the foreign key. The cascade option takes care of the records in the child table when an UPDATE or DELETE is performed on the parent table.

--Let's drop the foreign key and create it with a Cascade on Delete
ALTER TABLE dbo.Employees DROP CONSTRAINT FK_Employees_Companies
GO
 
ALTER TABLE dbo.Employees ADD CONSTRAINT
   FK_Employees_Companies FOREIGN KEY (CompID) REFERENCES dbo.Companies (ID) 
      ON UPDATE  NO ACTION 
      ON DELETE  CASCADE
GO
 
--Now what will happen when Company with ID = 1 (Alpha Company) closes down?
--Let's remove Company with ID = 1 (Alpha Company) from the Companies table.
 
SELECT * FROM Companies
SELECT * FROM Employees
 
DELETE FROM Companies where ID = 1
 
SELECT * FROM Companies
SELECT * FROM Employees

In the code above, when the company is deleted it will also delete any Employees rows that are tied to the company using the foreign key based on the DELETE CASCADE option that was specified.

Again, when the rules are set up correctly, the database will protect the data integrity.

Clean Up by Dropping the HRDatabase Database

You can run the code below to drop the sample database that we created.

USE master;
GO
 
--Drop Database if it exists
DROP DATABASE IF EXISTS HRDatabase;
GO

Next Steps

The next step is looking at how to create all supporting tables needed in a database to support a simple web application.

You can find additional reading at the following links:

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-08-09

Create SQL Server Tables Using Foreign Keys for Referential Integrity