Create,check and drop FOREIGN KEY CONSTRAINT on SQL SERVER table

Description: In this article you will learn following:
  • What is FOREIGN KEY?
  • How to create FOREIGN KEY CONSTRAINT on SQL SERVER Table
  • How to create FOREIGN KEY CONSTRAINT on existing table using ALTER and ADD command
  • How to check PRIMARY,FOREIGN,UNIQUE KEY and other CONSTRAINTS defined on table 
  • How to remove/drop FOREIGN KEY CONSTRAINT from table using ALTER and DROP command

What is a Foreign Key?

In simple words A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
or
A foreign key is a column(s) in one table that references a unique column usually the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.
The referenced table is called the parent table while the table with the foreign key is called the child table.

Implementation: Let’s understand the concept of foreign key constraint by implementing it on tables. For this we are going to create two table tbDepartment and tbEmployee.

First create a parent Table "tbDepartment" containing primary key "DeptId" that will become foreign key in child Table "tbEmployee"

CREATE TABLE tbDepartment
 (
                 DeptId INT IDENTITY(1,1) PRIMARY KEY, --define primary key
                 DeptName varchar (50) NOT NULL
 )
  • Insert some data in tbDepartment table using following queries

INSERT INTO tbDepartment(DeptName) VALUES ('SALES')
INSERT INTO tbDepartment(DeptName) VALUES ('ACCOUNTS')
INSERT INTO tbDepartment(DeptName) VALUES ('HRM')

  • Check inserted data using the query

SELECT * FROM tbDepartment

It will be as: 
DeptId
DeptName
1
SALES
2
ACCOUNTS
3
HRM

  • Now create Child Table "tbEmployee" containing foreign key "DeptId" which is the primary key in "tbDepartment" parent table

I have mentioned two ways to create foreign key in a table. You can use any of the two.

First way:

CREATE  TABLE tbEmployee
 (
                 EmpId INT IDENTITY(1,1) PRIMARY KEY, --define primary key
                 FirstName VARCHAR (50) NOT NULL,
                 LastName VARCHAR (50) NOT NULL,
                 Age       INT,
                 DeptId INT FOREIGN KEY REFERENCES  tbDepartment(DeptId) --define foreign key
 )

Or Second way:

 CREATE TABLE tbEmployee
 (
                 EmpId INT IDENTITY(1,1) PRIMARY KEY, --define primary key
                 FirstName VARCHAR (50) NOT NULL ,
                 LastName VARCHAR (50) NOT NULL,
                 Age       INT,
                 DeptId INT,      
                 CONSTRAINT FK_tbEmployee_tbDepartment FOREIGN KEY (DeptId) REFERENCES tbDepartment(DeptId) --define foreign key with name 
 )

Note: In first way we are not providing the name of our foreign key constraint . So it will automatically create a default name somewhat like "FK__tbEmploye__DeptI__0EA330E9" for the foreign key constraint . But in second way we are specifying the name of the foreign key constraint (FK_tbEmployee_tbDepartment).

  • Insert some data in tbEmployee table using following queries 

INSERT INTO tbEmployee(FirstName,LastName,Age,DeptId) VALUES ('Aryan','Thakur',25,1)
INSERT INTO tbEmployee(FirstName,LastName,Age,DeptId) VALUES ('Simran','Kaur',24,1)
INSERT INTO tbEmployee(FirstName,LastName,Age,DeptId) VALUES ('Kunaal','Sharma',26,3)

Check inserted data using the query

SELECT * FROM tbEmployee

It will be as: 
EmpId
FirstName
LastName
Age
DeptId
1
Aryan
Thakur
25
1
2
Simran
Kaur
24
1
3
Kunaal
Sharma
26
3

Note: DeptId in tbEmployee table is defined as foreign key which is the primary key of tbDepartment table. It means DeptId in tbEmployee table refers to DeptId in tbDepartment table.

Congrats you have defined foreign key constraint.  To check the defined constraint  read the article How to check PRIMARY,FOREIGN,UNIQUE and other KEY CONSTRAINTS in sql server table

How to create FOREIGN KEY Constraint on existing table

What if we have created the child table tbEmployee without defining the foreign key constraint on DeptId column.   E.g.

CREATE TABLE tbEmployee
 (
                 EmpId INT IDENTITY(1,1) PRIMARY KEY, --define primary key
                 FirstName VARCHAR (50) NOT NULL,
                 LastName VARCHAR (50) NOT NULL,
                 Age       INT,
                 DeptId INT
 )

In above table we have not defined the foreign key constraint on the DeptId column. But still we can add foreign key constraint on existing table using the following two ways.

Syntax is

ALTER TABLE ChildTableName ADD FOREIGN KEY (ColumnName)
REFERENCES  ParentTablePersons(ColumnName)

For example: 

ALTER TABLE tbEmployee ADD FOREIGN KEY (DeptId)
REFERENCES  tbDepartment (DeptId)

Or

Syntax is:

ALTER TABLE ChildTableName ADD CONSTRAINT ForeignKeyConstraintName FOREIGN KEY (ColumnName)
REFERENCES  ParentTablePersons(ColumnName)

For example:

ALTER TABLE tbEmployee ADD CONSTRAINT  FK_tbEmployee_tbDepartment  FOREIGN KEY (DeptId)
REFERENCES tbDepartment (DeptId)

Note: In first way we are not providing the name of our forign key constraint. So it will automatically create a default name e.g. "FK__tbEmploye__DeptI__0EA330E9" for the foreign key constraint. But in second way we are specifying the name of the foreign key constraint(FK_tbEmployee_tbDepartment).

How to DROP a FOREIGN KEY Constraint
Suppose for some reason you need to drop the foreign key constraint then you can use the alter command to drop the constraint
The syntax is

ALTER TABLE TableName
DROP CONSTRAINT ForeignKeyConstraintName

For example

ALTER TABLE tbEmployee
DROP CONSTRAINT FK_tbEmployee_tbDepartment


Points to remember:

1) The FOREIGN KEY constraint prevents the actions that could destroy links between tables.

For example:
DELETE FROM tbDepartment WHERE DeptId=1

In above query I am trying to delete the department whose id is 1 but since there are records in tbemployee table that refers the DeptId 1 of  tbDepartment table, following error occurs

The DELETE statement conflicted with the REFERENCE constraint "FK_tbEmployee_tbDepartment". The conflict occurred in database "DbEmp", table "dbo.tbEmployee", column 'DeptId'.

2)  The value entered into the column of a FOREIGN KEY constraint must either exist in the referenced column or it could be NULL; otherwise, a foreign key violation error message is returned. The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it must to be one of the values contained in the table it points to. So it maintains referential integrity - this means that there must be a corresponding record in the other table

For example execute the query

INSERT INTO tbEmployee(FirstName,LastName,Age,DeptId) VALUES ('Varun','Sharma',26,30)

Since there is no department with DeptId 30 in tbDepartment table, a foreign key violation error message is returned as:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbEmployee_tbDepartment". The conflict occurred in database " DbEmp ", table "dbo.tbDepartment", column 'DeptId'.

Also for example execute the query

INSERT INTO tbEmployee(FirstName,LastName,Age,DeptId) VALUES ('Hardeep','Singh',26,null)

Above query will run successfully

3) Besides referencing a primary key of another table, a foreign key can also reference a column that has the UNIQUE constraint.

4) FOREIGN KEY constraints can be defined to refer only tables within the same database on the same server

5) The data type of the both the participating columns must be same

6) We can’t define foreign key constraints on temporary tables.

Now over to you:
" I hope you have got the way to create foreign key constraint on sql server table and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..