Foreign Key in SQL Server

Hello Friends, In this tutorial, I am going to tell you about Foreign Key in SQL Server. We will also see how to create a Foreign Key in SQL Server. We have already discussed Primary Key in the previous session.

After Completing this tutorial you will be able to understand:

• Foreign Key in SQL Server.
• How to create a Foreign Key in SQL Server.

Introduction:

Foreign Key is also known as a referencing key. It is used to link two tables together. Foreign Key specifies that the value in one table must appear in the second table also. The relationship between two tables is described by the Primary Key in one table and Foreign Key in another table. The table that contains the Primary key is called the Parent Table and the table that contains the Foreign Key is called the Child Table.

The syntax for creating Foreign Key in SQL Server:

CREATE TABLE yourchildtable
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fkname
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parenttable (parentcol1, parentcol2, ... parentcol_n)
    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

WHERE;

childtable:

The name of the child table that you wish to reference with parent table.

column1, column2:

The columns that you want to create in the child table.

fkname:

The name of the Foreign Key Constraint that you want to create.

childcol1, childcol2, … childcol_n:

The columns in childtable that will reference a primary key in the parent_table.

parenttable:

The name of the parent table whose primary key will be used in the childtable.

parentcol1, parentcol2, … parentcol3:

The columns that make up the primary key in the parent_table. The foreign key will enforce a link between this data and the child_col1, child_col2, … child_col_n columns in the child_table.

ON DELETE

Optional. This specifies that what need to do with childtable when the data from the parent table is deleted. You can set one of the values such as  NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

ON UPDATE:

This is also optional Optional. This specifies that what to do with the child data when the data from the parent table is updated. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

NO ACTION:

This specifies that no action is performed with the child data when the data from the parent table is deleted or updated.

CASCADE:

This specifies that the child data is either deleted or updated when the data from the parent table is deleted or updated.

SET NULL:

This specifies that the child data is set to NULL when the data from the parent table is deleted or updated.

SET DEFAULT:

This specifies that the child data is set to their default values when the data from the parent table is deleted or updated.

How to create Foreign Key in SQL Server?

For this let’s create a table that will contain the Primary Key.

CREATE TABLE Employee(
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailID VARCHAR(150),
MobileNo VARCHAR(50)
)

You can see in the above table structure Employee table have EmployeeID as a Primary Key. Now suppose I need to store Attendance of these employees. So we can create a relationship like this.

CREATE TABLE EmployeeAttendence

(

ID INT PRIMARY KEY,
AttendenceDate DATETIME,
EmployeeID INT,
Status INT,
CONSTRAINT fkEmployeeID
FOREIGN KEY(EmployeeID)
REFERENCES Employee(EmployeeID)
)

You can see from the above script we have referenced an EmployeeID column from the Employee table.

How to create Foreign Key on multiple columns.

Let’s create an Employee table with Primary Key on a group of columns like EmailID and Mobile No. Here is the script for that.

CREATE TABLE Employee
(
EmployeeID INT ,

FirstName VARCHAR(50),

stName VARCHAR(50),

EmailID VARCHAR(150),

MobileNo VARCHAR(50),

CONSTRAINT pkEmployee PRIMARY KEY(EmailID,MobileNo)
)

Now for Referencing these two columns with the EmployeeAttendence table, we need to create the EmployeeAttendence table in the following way.

CREATE TABLE EmployeeAttendence
(
ID INT PRIMARY KEY,
AttendenceDate DATETIME,
EmailID VARCHAR(150),
MobileNo VARCHAR(50),
Status INT,
CONSTRAINT fkEmployeeID
FOREIGN KEY(EmailID,MobileNo)
REFERENCES Employee(EmailID,MobileNo)
)

As you can see from the above script I have referenced EmailID and MobileNo columns from the Employee Table.

Create a Foreign Key after the creation of table:

The syntax for creating Foreign Key with ALTER TABLE Statement is:

ALTER TABLE childtable
ADD CONSTRAINT fkname
    FOREIGN KEY (childcol1, childcol2, ... childcol_n)
    REFERENCES parenttable (parentcol1, parentcol2, ... parentcol_n);

WHERE;

childtable:

This specifies the name of the Child Table that we want to reference with Parent Table.

fkname:

fkname specifies the name of the Foreign Key CONSTRAINT.

childcol1, childcol2, … childcol_n:

This specifies the name of the column that we want to reference with parent table.

parenttable:

This specifies the name of the Parent Table that contains the Primary Key.

parentcol1, parentcol2, … parentcol_n:

This specifies the columns marked as Primary Key in Parent Table.

Example:

ALTER TABLE EmployeeAttendence
ADD CONSTRAINT fkEmployee FOREIGN KEY(EmployeeID)
REFERENCES Employee(EmployeeID)

 Conclusion:

Hope you understand the concept of Foreign Key in SQL Server. I would like to have your Comments, Feedback and Suggestions:

View More:

• Primary Key in SQL Server.
• Stored Procedure in SQL Server.

Thank You.