SQLServerLog.com

SQL FOREIGN KEY Constraint

by · Published · Updated

ForeignKeySQL FOREIGN KEY constraint represents relationship between tables. A Foreign Key is a column(s) whose values are derived from the PRIMARY KEY or UNIQUE KEY of some other table.

If you want to associate records of one table with records of another table then you can create relationship between two tables with FOREIGN KEY. A column level FOREIGN KEY constraint can list only one reference column and data type of this column must be same with defined constraint on that column and also you cannot enforce FOREIGN KEY on temporary tables.

To understand more easily, we will consider two tables, tblProject and tblEmployee as follows. We have created ProjectID column as primary key in table tblProject and same column is defined as referencing foreign key in table tblEmployee.

Table Name: tblProject

CREATE TABLE tblProject
(
ProjectID int NOT NULL PRIMARY KEY,
ProjectName VARCHAR(50) NOT NULL,
COUNTRY VARCHAR(50)
)

Table Name: tblEmployee

CREATE TABLE tblEmployee 
(
EmpID int NOT NULL PRIMARY KEY,
ProjectID int FOREIGN KEY REFERENCES tblProject (ProjectID),
ProjectName VARCHAR(50) NOT NULL,
DOJ DATETIME,
ADDRESS VARCHAR (100)
)

Now, we will insert some records in both the tables.

Insert records in table tblProject:

INSERT INTO tblProject
VALUES (9001, 'AMERICAN BANK', 'USA')
GO

INSERT INTO tblProject
VALUES (9002, 'AMERICAN HEALTHCARE', 'GERMANY')
GO

INSERT INTO tblProject
VALUES (9003, 'TATA CONSULTANCY', 'INDIA')
GO

Insert records in table tblEmployee:

INSERT INTO tblEmployee
VALUES (100001, 9001, 'AMERICAN BANK', GETDATE(),'1056, USA')
GO

INSERT INTO tblEmployee
VALUES (100002, 9002, 'AMERICAN HEALTHCARE', GETDATE(), '100, USA')
GO

INSERT INTO tblEmployee
VALUES (100003, 9003, 'TATA CONSULTANCY', GETDATE(),'MUMBAI, INDIA')
GO

As you can see, column records in ProjectID column is same as this is primary key in first table and foreign key in second table.

Now, we will try to insert one record in tblEmployee table.

INSERT INTO tblEmployee
VALUES (100004, 9004, 'INFOSYS', GETDATE(),'PUNE, INDIA')
GO

 It will not allow you to insert record in tblEmployee table and you will get below error message because associated record is not available in table tblProject.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__tblEmploy__Proje__17F790F9". The conflict occurred in database "COLLEGE", table "dbo.tblProject", column 'ProjectID'.
The statement has been terminated.

So, we will first insert record in table tblProject then again will try to insert same record in tblEmployee table.

INSERT INTO tblProject
VALUES (9004, 'INFOSYS', 'INDIA')

Now try to insert same record and it will allow you to insert.

INSERT INTO tblEmployee
VALUES (100004, 9004, 'INFOSYS', GETDATE(),'PUNE, INDIA')
GO

I hope you have understood basic concept of FOREIGN KEY. Once you will practice, you will understand it clearly.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

You may also like...

Leave a Reply