Foreign Key Constraint in SQL

sqlTo maintain database integrity, Foreign Key constraints are used.  A column in a table which is marked as Foreign Key will always point to the Primary Key column of another table.  Lets understand this concept with an example.  First, we will create 2 tables tblEmployee and tblGender.  Syntax for creating tblEmployee and tblGender is given below.  Please check out INSERT INTO statement tutorial for adding records to it.

tblEmployee:

Create Table tblEmployee
(
ID int NOT NULL Primary Key,
Name nvarchar(50),
Email nvarchar(50),
Gender int
)

tblGender:

Create Table tblGender
(
ID int NOT NULL Primary Key,
Gender nvarchar(50) NOT NULL
)
tblEmployee
ID NAME EMAIL GENDER
1 Albert albert@sample.com 1
2 Robert robert@sample.com 1
3 Peter peter@sample.com 1
4 Susan susan@sample.com 2
5 Mary mary@sample.com 3
tblGender
ID GENDER
1 Male
2 Female
3 Unknown

 

Now, we will establish a Foreign Key relationship between these 2 tables.  We will mark Gender column as Foreign Key in tblEmployee and it will point to the Primary Key column ID of tblGender.  Syntax for adding Foreign Key constraint is given below.

ALTER TABLE tblEmployee ADD CONSTRAINT tblEmployee_Gender_FK
FOREIGN KEY (Gender) REFERENCES tblGender (ID)

After adding Foreign Key constraint, you cannot add any other values in Gender column of tblEmployee which does not exist in Primary Key column ID of tblGender.  You can test this by using INSERT INTO statement in tblEmployee and providing any value for last Gender column apart from 1, 2, and 3.  In layman’s term, this will prevent us from adding any invalid value or record in Foreign Key column.

NOT WORK:

INSERT INTO tblEmployee (ID, NAME, Email, Gender)
VALUES (6,'Aria', 'aria@sample.com', 4)