What is FOREIGN KEY Constraint in SQL Server?

Introduction:

In this article i will explain what is FOREIGN KEY Constraint in SQL Server.

Description:

In previous articles i explained what is Constraint, how many Constraints available in SQL Server, and what is NOT NULL, CHECK, UNIQUE, and PRIMARY KEY Constraint in SQL Server. Now i will explain what is FOREIGN KEY Constraint in SQL Server.

FOREIGN KEY Constraint:

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

Example:

Department Table:

This is our parent table. In this DeptNo is Primary Key. There are three rows present in that table.

Employee Table:

This is our child table. In this EmpNo is Primary Key, and DeptNo is Foreign Key. Here we can insert data for EmpNo 1 and 2. When we try to insert data for EmpNo 3 we get error. DeptNo = 105 can not be inserted into Employee(child) table because DeptNo = 105 not present in the Department(parent) table. These operations are done by Foreign Key.

Syntax:

Department:

-- Create 'Department' table
Create table Department
(
    DeptNo int primary key identity(101,1),
    DeptName varchar(50)
)

-- Insert data into 'Department' table.
insert into Department values('Finance')
insert into Department values('Production')
insert into Department values('Marketing')

-- Select data from 'Department' table.
select * from Department

Output:

Employee:

-- Create 'Employee' table
Create table Employee
(
    EmpNo int primary key identity(1,1),
    EmpName varchar(50),
    EmpSalary decimal,
    DeptNo int constraint FK_DeptNo references Department(DeptNo)
)

-- Insert data into 'Employee' table.
insert into Employee values('AAA', 1000, 101)
insert into Employee values('BBB', 2000, 102)

-- Select data from 'Employee' table.
select * from Employee

Output:

insert into Employee values('CCC', 3000, 105)

DeptNo = 105 can not be inserted into Employee(child) table because DeptNo = 105 not present in the Department(parent) table. We will get following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DeptNo". The conflict occurred in database "Test", table "dbo.Department", column 'DeptNo'.

Note:

1. We can enter the values in to the child table which are present in parent table only.
2. The data type of both the columns is same.
3. The column of parent table should be a PRIMARY KEY or UNIQUE KEY.
4. We can't delete/update a record from parent table which has reference in the child table.