Foreign Key in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Foreign Key Constraint in SQL Server with Examples

In this article, I am going to discuss the Foreign Key Constraint in SQL Server with Examples. Please read our previous article where we discussed the Primary Key Constraint in SQL Server with examples before proceeding to this article. As part of this article, we are going to discuss the following important concepts.

1. What is a Foreign Key in SQL Server?
2. How to create Foreign Key in SQL Server?
3. How to Maintain the Primary Key and Foreign Key relationship in SQL Server?
4. Imposing Foreign key constraint at the column level.
5. How to Create Foreign Key Constraint in SQL Server at Table Level?
6. What is the difference between primary key and foreign key in SQL Server?

What is a Foreign Key Constraint 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 them in an efficient manner.

In order to create a link between two tables, we must specify a Foreign Key in one table that references 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.

How to Create Foreign Key Constraint in SQL Server?

To Create 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 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. 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 the Foreign key column and we need to impose a Foreign key constraint on the column which refers to the 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 the 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

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 error as 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 error as 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.

How to Create Foreign key constraint in SQL Server at the column level?

CREATE TABLE Employee 
( 
    Empid  INT, 
    Ename  VARCHAR(40), 
    Job    VARCHAR(30), 
    Salary MONEY, 
    Deptno INT CONSTRAINT deptn0_fk REFERENCES Dept(Dno)
)

How to create Foreign key Constraint 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 creating a Foreign key constraint at table level we need to use explicitly the Foreign key clause whereas it is not required while creating at 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

Rule1: 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

Rule2: Cannot update the reference key value of a parent table provided that the value has a 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

Rule3: 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 Constraint in SQL Server?

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 that will physically organize 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.

Foreign Key in SQL Server:

1. The Foreign Key in SQL Server is a field in a table that is a 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 the 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 references 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 references a key that should contain unique values. So it may be a primary key or unique key as both keys maintain 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 unique or non-primary keys which may hold NULL values.

In the next article, I am going to discuss how to make the Primary Key and Foreign Key relationship between more than two tables. Here, in this article, I try to explain Foreign Key Constraint in SQL Server with Examples.