SQL Server FOREIGN KEY

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the FOREIGN KEY integrity constraint to establish a referential link between 2 tables in a database.

What is FOREIGN KEY in SQL Server?

A PRIMARY KEY uniquely identifies every row of information in a table (also called a record or tuple). The PRIMARY KEY of one table serves as the FOREIGN KEY of another table. The table to which the PRIMARY KEY originally belongs is called the parent table and the table which refers that PRIMARY KEY is called the child table. The link between the two tables is called a referential link and the purpose of establishing it is to enforce referential integrity.  

Referential integrity simply means

  • that we cannot add any data in the child table for which corresponding data does not exist in the parent table.
  • that we cannot modify data in the parent table in a manner that data in the child table is distorted or orphaned.

Since the PRIMARY KEY of the parent table becomes the FOREIGN KEY of the child table it follows that a FOREIGN KEY shares the same properties as a PRIMARY KEY (i.e. it can be single or multicolumn and it cannot have duplicate or NULL values). While defining the FOREIGN KEY in the child table we can define actions on the FOREIGN KEY column corresponding to actions on the column in the parent table. This is defined with the help of ON DELETE and ON UPDATE clauses which specify how to handle the data in the child table corresponding to data modifications in the parent table. There are 4 options available to control what action will be performed in the child table whenever data in the parent table is deleted or updated. They are the below.

  1. NO ACTION – SQL server throws an error and does not perform the requested action.
  2. CASCADE – SQL server replicates the change made in the parent table to the child table.
  3. SET DEFAULT – SQL server sets a default value (which must be predefined) in the child table for modifications made in the parent table.
  4. SET NULL – SQL server sets NULL value in the child table for modifications made in the parent table.

Operation

A FOREIGN KEY can be defined on a column or multiple columns in the child table while creating a table or later. It is also possible to drop the FOREIGN KEY in a table if not required.

SQL Server FOREIGN Key Syntax

The basic syntax of adding FOREIGN KEY using CREATE TABLE and ALTER TABLE statements and dropping FOREIGN KEY using DROP TABLE statement is given below.

CREATE TABLE Syntax

Syntax without Constraint Name

CREATE TABLE child_table
(child_col1 DATATYPE PRIMARY KEY,
child_col2 DATATYPE,
child_col3 DATATYPE,
…………………………………,
FOREIGN KEY (child_col11, child_col2));
REFERENCES parent_table (parent_col1, parent_col2)
[ON DELETE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)]
[ON UPDATE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)]

The following CREATE table syntax creates a foreign key is without using a constraint name.

Syntax with Constraint Name

The following CREATE table syntax creates a FOREIGN KEY with a constraint name. This is the best practice because a constraint name helps in easy identification and management.

CREATE TABLE child_table
(child_col1 DATATYPE PRIMARY KEY,
child_col2 DATATYPE,
child_col3 DATATYPE,
…………………………………,
CONSTRAINT constraint_name
FOREIGN KEY (child_col11, child_col2));
REFERENCES parent_table (parent_col1, parent_col2)
[ON DELETE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)]
[ON UPDATE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)]

ALTER TABLE Syntax

The following ALTER TABLE syntax adds a FOREIGN KEY to the child table after table creation.

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (child_col1, child_col2)
REFERENCES parent_table (parent_col1, parent_col2)
[ON DELETE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)]
[ON UPDATE (NO ACTION | CASCADE | SET NULL | SET DEFAULT)];

DROP TABLE Syntax

The following ALTER TABLE syntax drops a FOREIGN KEY in the child table through the constraint name.

ALTER TABLE tablename
DROP CONSTRAINT constraint_name;

In this syntax,

  • CREATE – clause used to create a database object (like table, index, schema, etc.).
  • child_table – table on which the FOREIGN KEY is defined.
  • child_col– column belonging to the child table.
  • DATATYPE – keyword identifying the type of the data i.e. whether int, string, date, etc.
  • PRIMARY KEY – keyword combination to tell SQL that the column or columns that follow are non-null, unique valued columns which should be treated as the KEY for the table.
  • CONSTRAINT – optional. Keyword to tell SQL that what follows is an integrity constraint.
  • constraint_name – optional. A name to identify the constraint that follows.
  • FOREIGN KEY –keyword combination to tell SQL that the column or columns that follow are a replication of the parent table PRIMARY KEY columns.
  • REFERENCES – keyword to specify the parent table columns.
  • parent_table – table being referred to from the child table.
  • parent_col – column belonging to the parent table.
  • ON DELETE –keyword combination to tell SQL what action to perform on the child table for a delete operation in the parent table.
  • ON UPDATE – keyword combination to tell SQL what action to perform on the child table for an update operation in the parent table.
  • NO ACTION – keyword combination to tell SQL that an action should not be performed.
  • CASCADE – keyword combination to tell SQL to replicate the parent table change to the child table FOREIGN KEY column(s).
  • SET DEFAULT – keyword combination to tell SQL to set the FOREIGN KEY column(s) value as DEFAULT.
  • SET NULL – keyword combination to tell SQL to set the FOREIGN KEY column value(s) as NULL.
  • ALTER – clause used to modify a database object (e.g. table, schema, index etc.).
  • ADD – clause used to add something to a database object (like adding a column or constraint to a table).
  • DROP – clause used to remove something from a database object (like dropping columns or constraints from a table) or to delete a database object altogether (like dropping table).

SQL Server FOREIGN KEY Examples

To understand the significance and practical implications of the FOREIGN KEY and the referential link it establishes let us first create two tables. We will create a departments table (holding data of the different departments) as the parent table and an employees table (holding the different department employee’s information) as the child table. The employees table foreign key will refer the departments table primary key and the FOREIGN KEY column will have a default value 200. The following CREATE TABLE statements will do the same.

CREATE TABLE departments
(dept_id TINYINT PRIMARY KEY,
dept_name VARCHAR(30));
CREATE TABLE employees
(emp_id TINYINT IDENTITY(201,1) PRIMARY KEY,
dept_id TINYINT DEFAULT 100,
first_name VARCHAR(50),
last_name VARCHAR(50),
emp_gender CHAR(1),
CONSTRAINT table_fk
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE NO ACTION
ON UPDATE CASCADE);

  After table creation and data insertion we have two tables as below.

dept_namedept_id
Human Resource101
Procurement102
Facilities & Administration103
Table: departments
emp_iddept_idfirst_namelast_nameemp_gender
201101ShwetaNaikF
202103JacinthaJosephF
203102AnkurTripathiM
204102DigvijaySharmaM
205101ZakiyaKhanumF
Table: employees

1)  SQL Server FOREIGN KEY – child table INSERT example

As discussed above we cannot insert any data in the child table for which corresponding data does not exist in the parent table. What it practically means in our scenario is that we cannot add any employee record in the employees table who does not belong to any of the departments in the departments table. Let us try to do the same. The following query tries to add an employee to a non-existent dept_id 105. The result is that we get a FOREIGN KEY constraint violation error and the query fails.  Please note the constraint name and column name mentioned in the error message.

INSERT INTO employees
(dept_id, first_name, last_name, emp_gender)
VALUES
(105, 'Deep', 'Bose', 'M');
Msg 547, Level 16, State 1, Server MY-HP245G6, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "table_fk". The conflict occurred in database "master", table "dbo.departments", column 'dept_id'.
The statement has been terminated.

2)  SQL Server FOREIGN KEY – NO ACTION and CASCADE example

Now we will check out the ON DELETE NO ACTION clause which we defined on the FOREIGN KEY while creating the table by trying to delete a department from the departments table. The following delete query tries to do the same but fails with a FOREIGN KEY constraint violation error as expected.

DELETE FROM departments
WHERE dept_id=101;
Msg 547, Level 16, State 1, Server MY-HP245G6, Line 1
The DELETE statement conflicted with the REFERENCE constraint "table_fk". The conflict occurred in database "master", table "dbo.employees", column 'dept_id'.
The statement has been terminated.

Now we will update the dept_id for one department to check the ON UPDATE CASCASE clause that we specified on the FOREIGN KEY while creating the table. The following query does the same successfully.

UPDATE departments
SET dept_id=104
WHERE dept_id=103;

We can check the result by running SELECT queries on the departments and employees table. We can see that the dept_id has been updated both in the departments and employees table for the relevant employee ‘Jacintha Joesph’.

dept_namedept_id
Human Resource101
Procurement102
Facilities & Administration104
emp_iddept_idfirst_namelast_nameemp_gender
201101ShwetaNaikF
202104JacinthaJosephF
203102AnkurTripathiM
204102DigvijaySharmaM
205101ZakiyaKhanumF

3) SQL Server FOREIGN KEY – SET NULL and SET DEFAULT example

We will now check the other 2 options that can be set with ON UPDATE and DELETE. But before doing so we will first drop the FOREIGN KEY constraint from the child table with the following ALTER TABLE query.

ALTER TABLE employees
DROP CONSTRAINT table_fk;

Now we will freshly add the FOREIGN KEY with a new constraint name and new conditions for ON DELETE and ON UPDATE to check out the SET NULL and SET DEFAULT options. The following ALTER TABLE query does the same.

ALTER TABLE employees
ADD CONSTRAINT new_foreign_key
FOREIGN KEY (dept_id)
REFERENCES departments (dept_id)
ON DELETE SET NULL
ON UPDATE SET DEFAULT;

We can check the newly added constraint with the following command in SSMS (SQL Server Management Studio).

EXEC SP_HELP employees;

It will show the constraint definition at the bottom of the output as below.

Now we will delete the ‘Facilities & Administration’ department with dept_id 104 from the departments table with the following DELETE query to check the ON DELETE SET NULL condition.

DELETE FROM departments
WHERE dept_id=104;

After deletion we can check the result by running SELECT queries on the departments and employees table. We can see that the ‘Facilities & Administration’ department does not exist anymore and dept_id for employee ‘Jacintha Joesph’ is now NULL as per the ON DELETE action specified.

dept_namedept_id
Human Resource101
Procurement102
emp_iddept_idfirst_namelast_nameemp_gender
201101ShwetaNaikF
202NULLJacinthaJosephF
203102AnkurTripathiM
204102DigvijaySharmaM
205101ZakiyaKhanumF

Now we will try the last case of ON UPDATE SET DEFAULT by updating the dept_id for the ‘Procurement’ department. The following UPDATE query does the same.

UPDATE departments SET dept_id=106 WHERE dept_id=102;

After the update, we can check the results by running queries on both the departments and employees table which will show the below. As we can see the dept_id for the ‘Procurement’ dept has been updated to 106 in the departments table and dept_id has been set to the default value of 100 as we had set during the creation of the table for the relevant employee records in the employees table.

dept_namedept_id
Human Resource101
Procurement106
emp_iddept_idfirst_namelast_nameemp_gender
201101ShwetaNaikF
202NULLJacinthaJosephF
203100AnkurTripathiM
204100DigvijaySharmaM
205101ZakiyaKhanumF

 

Advertisement