Introduction: In this article I am
going to share what is foreign key and how to define/add/create/implement foreign
key constraint on sql server table.
In previous articles i explained How to
In previous articles i explained How to
Description: In this article you will learn following:
What is a Foreign Key?
- 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
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:
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..