Feb 12, 2013

Foreign key constraint sql server | Referential constraint


Before we start to discuss about foreign key constraint I want to ask few questions:

Why we create foreign key constraint between two tables?

1. Can we write join query between two tables which has not foreign key constraints?
2. Foreign key constraints improve the query performance? 
 
Let's check it one by one. We are creating tables and inserting some records into it:

CREATE TABLE tblEmployee(
    ntEmployeeID  BIGINT PRIMARY KEY IDENTITY,
    vcName VARCHAR(100)
)

CREATE TABLE tblEmpDetial(
    ntEmpDetialID BIGINT PRIMARY KEY IDENTITY,
    ntEmployeeID  BIGINT,
    ntAge int
)

We are not creating foreign key constraints between two tables.  Now we are going to execute join query:

SELECT *
FROM tblEmployee E
INNER JOIN tblEmpDetial ED
ON E.ntEmployeeID = ED.ntEmployeeID

It executed successfully. No error!!
So there is no need to create foreign key constraint to write join queries.

Now we are creating two similar tables. Only  differences is there is foreign key constrain between two tables:

CREATE TABLE tblEmployeeFk(
    ntEmployeeID  BIGINT PRIMARY KEY IDENTITY,
    vcName VARCHAR(100)
)

CREATE TABLE tblEmpDetialFk(
    ntEmpDetialID BIGINT PRIMARY KEY IDENTITY,
    ntEmployeeID  BIGINT REFERENCES tblEmployeeFk,
    ntAge int
)

Now we are inserting some records into tblEmployee and tblEmployeeFk.

INSERT tblEmployee VALUES('Scott'),('Greg'),('Alan')
INSERT tblEmployeeFk  VALUES('Scott'),('Greg'),('Alan')

Now we are going to insert some records into tblEmpDetial  and tblEmpDetialFk. Here difference is table tblEmpDetialFk has referential integrity but tblEmpDetial  has not any referential integrity. We will use actual execution plan to compare the performance of both queries:

--First: Without foreign key constraint
INSERT tblEmpDetial VALUES(1,18),(2,21),(3,34)

--Second: With foreign key constraint
INSERT tblEmpDetialFk  VALUES(1,18),(2,21),(3,34)

Actual execution plan:


Query cost of first insert statement is only 42% compare to second one which has 58%. It concludes:
Foreign key constraint decrease the performance on insert statement.

If you will observe the execution plan of second insert statement that is which has foreign key constraint has to pay extra cost to check  the data of the ntEmpID  field of the referenced table (tblEmployee)

Effect of foreign key constrain in update statement:

--First: Without foreign key constraint
UPDATE tblEmpDetial SET ntEmployeeID  = 1

--Second: With foreign key constraint
UPDATE tblEmpDetialFk SET ntEmployeeID = 1

Actual execution plan:


Again,  Foreign key constraint decrease the performance on update statement.

Effect of foreign key constrain in join query:

--First: Without foreign key constraint
SELECT *
FROM tblEmployee E
INNER JOIN tblEmpDetial ED
ON E.ntEmployeeID = ED.ntEmployeeID

--Second: With foreign key constraint
SELECT *
FROM tblEmployeeFk E
INNER JOIN tblEmpDetialFk ED
ON E.ntEmployeeID = ED.ntEmployeeID

Actual execution plan:


From execution plan it is clear for the join query foreign key constraint doesn't effect the query performance.

Effect of foreign key constrain in sub query:

--First: Without foreign key constraint
SELECT *
FROM tblEmpDetial
WHERE ntEmployeeID IN (SELECT ntEmployeeID FROM  tblEmployee)

--Second: With foreign key constraint
SELECT *
FROM tblEmpDetialFk

No comments:

Post a Comment