Foreign Key in SQL Server

Foreign Key in SQL Server

In this article, I am going to discuss Foreign Key in SQL Server with some examples. Please read our previous article where we discussed the Primary Key Constraint in SQL Server with examples before proceeding to this article. 

FOREIGN KEY in SQL Server

One of the most important concepts in a database is, creating the relationship between the database tables. This relationship provides a mechanism for linking the data stores in multiple tables and retrieving it in an efficient manner.

In order to create a link between two tables, we must specify a Foreign Key in one table that references to a column in another table. That means Foreign Key constraint is used for binding two tables with each other and then verify the existence of one table data in other tables.

A foreign key in one TABLE points to a primary key or unique key in another table. The foreign key constraints are used to enforce referential integrity.

To impose a FOREIGN KEY Constraint in SQL Server we require the following things
  1. We require two tables for binding with each other and those two tables must have a common column for linking the tables.
  2. The common column that is present in both the tables need not to have the same name but their data type must be the same.
  3. The common column that is present under the parent table or master table is known as the reference key column and moreover, the reference key column should not contain any duplicate values in it. So we need to impose either UNIQUE or PRIMARY key constraint on that column.
  4. If we impose the primary key constraint on the reference key column that column will also be the identity column of the table.
  5. The common column which is present in the child or detailed table is known as Foreign key column and we need to impose a Foreign key constraint on the column which refers to reference key column of the master table.
The general formula is

Alter table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)

Creating PRIMARY KEY and FOREIGN KEY relation on two tables.

Create a table with the name as DEPT by using PRIMARY KEY constraint (Parent table)

CREATE TABLE Dept 
( 
    Dno   INT PRIMARY KEY, 
    Dname VARCHAR(30), 
    Dloc  CHAR(40) 
) 
GO

Now insert some values into Parent table like below

INSERT Dept VALUES (10, '.NET', 'HYD') 
INSERT Dept VALUES (20, 'JAVA', 'PUNE') 
INSERT Dept VALUES (30, 'PHP', 'DELHI') 
Go

Select * from Dept
Go

Foreign Key Constraint in SQL Server - Master Table

Creating another table with the name as Employee by using FOREIGN KEY constraint (Child table)

CREATE TABLE Employee 
( 
    Eid    INT PRIMARY KEY, 
    Ename  VARCHAR(30), 
    Salary MONEY, 
    Dno    INT FOREIGN KEY REFERENCES Dept(Dno) 
) 

Now insert values into the Employee Table to understand the power of Foreign Key

INSERT into Employee VALUES (101,'AA', 25000, 10) -- Allowed
INSERT into Employee VALUES (102,'BB', 32000, 20) -- Allowed
INSERT into Employee VALUES (103,'CC', 52000, 40) -- Not Allowed

When we execute the last statement it will give us the below error

The INSERT statement conflicted with the FOREIGN KEY constraint “FK__Employee__Dno__164452B1”. The conflict occurred in database “TestDB”, table “dbo.Dept”, column ‘Dno’.

The statement has been terminated.

INSERT into Employee VALUES (104,’DD’, 62000, 30) — Allowed

INSERT into Employee VALUES (105,’EE’, 42000, 50) — Not Allowed

When we execute the above statement it will give us the below error

The INSERT statement conflicted with the FOREIGN KEY constraint “FK__Employee__Dno__164452B1”. The conflict occurred in database “TestDB”, table “dbo.Dept”, column ‘Dno’.

The statement has been terminated.

Imposing Foreign key constraint in column level:
CREATE TABLE Employee 
( 
    Empid  INT, 
    Ename  VARCHAR(40), 
    Job    VARCHAR(30), 
    Salary MONEY, 
    Deptno INT CONSTRAINT deptn0_fk REFERENCES Dept(Dno)
)
Imposing Foreign key in SQL Server at table level:
CREATE TABLE Employee 
( 
    Empid  INT, 
    Ename  VARCHAR(40), 
    Job    VARCHAR(30), 
    Salary MONEY, 
    Deptno INT, 
    CONSTRAINT deptno_fk FOREIGN KEY (Deptno) REFERENCES Dept(Dno) 
) 

While imposing a FOREIGN key constraint in table level we need to use explicitly the FOREIGN key clause whereas it is not required while imposing in column level because the constraint is defined beside the foreign key column only.

When we impose FOREIGN KEY constraint and establish the relation between tables the following 3 rules come into the picture

RULE 1: Cannot insert a value into the foreign key column provided that value is not existing in the reference key column of the parent (master) table.

Example:

INSERT into Employee VALUES (105,’EE’, 42000, 50) — Not Allowed

RULE 2: Cannot update the reference key value of a parent table provided that the value has corresponding child record in the child table without addressing what to do with the child records.

Example:

UPDATE DEPT SET DNO = 100 WHERE DNO = 10 — Not Allowed

RULE 3: Cannot delete a record from the parent table provided that records reference key value has child record in the child table without addressing what to do with the child record.

Example:

DELETE FROM DEPT WHERE DNO = 20 — Not Allowed

What is the difference between primary key and foreign key in SQL Server?

The primary key in SQL Server:

  1. The Primary Key Constraint in SQL Server uniquely identifies a record in the table.
  2. Primary Key constraint neither accepts null values nor duplicate values on the column on which it is applied.
  3. By default Primary Key Constraint in SQL Server create a unique clustered index which will physically organized the data in the table.
  4. We can create only one Primary Key on a table in SQL Server. The most important point is that you can create the primary key either on a single column or multiple columns.

The Foreign key in SQL Server:

  1. The Foreign Key in SQL Server is a field in a table which is an unique key in another table.
  2. A Foreign Key can accept both null values and duplicate values in SQL Server.
  3. By default, the foreign key does not create any index. If you need then you can create an index on the foreign key column manually.
  4. We can create more than one Foreign key on a table in SQL Server.

NOTE: We can’t insert foreign key column value into the table if the primary key value not available but the reverse is possible. We can’t delete primary key value if the foreign key reference is set into the table but the reverse is possible.

Can we create a table with multiple unique, foreign, or primary keys in SQL Server?

We can create a table in SQL Server with multiple unique and foreign keys. But it is not possible to create a table can with multiple primary keys in SQL Server.

Is it possible that a foreign key reference a non-primary key in SQL Server?

Yes, it is possible. The point that you need to keep in mind is that a foreign key actually reference a key which should contain unique values. So it may be a primary key or unique key as both keys maintains the uniqueness of the column of a table.

Can a foreign key accept null values in SQL Server?

Yes, a foreign key in SQL Server can accept NULL values. This is because a Foreign key can reference to unique or non-primary keys which may hold NULL values.

In the next article, I will discuss How to make Primary Key and Foreign Key relationship between more than two tables

SUMMARY

In this article, I try to explain Foreign Key in SQL Server step by step with some simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *