Foreign Key Constraint in SQL
To 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 | 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)
