Join Class

Foreign key in SQL Server

In this tutorial you will how to create foreign key in SQL table, create relation between primary key and foreign key in SQL table.

Foreign key constraint in sql

A foreign key in sql is a key used to link two SQL tables together known as sql foreign key relation.

The table holding the foreign key reference is called the child table

Let’s look at the picture below
Here table "tbClassTeacherMap" is having a foreign key column called "TeacherId" , and the Foreign key constraint name is "FK_ tbClassTeacherMap_tbTeacher"

foreign key in sql

SQL Foreign key Characteristic

  • In one SQL table we can have multiple FOREIGN KEY reference
  • Can a Foreign key value be NULL? Yes, we can have null value in FOREIGN KEY column, but in most of the business scenario you may always have some values in that FOREIGN KEY column, because the column value deciding a relation, but the value still can be null
  • Can a Foreign key value be duplicate? Yes, we can duplicate value in FOREIGN KEY column, but think logically, what would happen if we have duplicate value in that column when some other data relation will depend on that value. You will get the same duplicate result for duplicate foreign key value, but still you can have duplicate value
  • The FOREIGN KEY column establishes a parent-child relationship, also known as "Referential Integrity."
  • The Table that has FOREIGN KEY column is called child table
How to create foreign key in SQL Table

So far you have understood what is Foreign key column, and what is Foreign Key Constraint, Now you learn how to create Foreign Key Constraint.

You can create Foreign Key two ways
  • At the time of Table Creation

    CREATE TABLE ClassStudentMap (
    CSId	int not null identity(1,1)  PRIMARY KEY,
    ClassId int NOT NULL,
    StudentId bigint NOT NULL,
    CONSTRAINT FK_ClassStudentMap_Student FOREIGN KEY (StudentId)
    REFERENCES tbStudent(StudentId)
  • You also can Alter the Table to add FOREIGN KEY reference

    alter TABLE ClassStudentMap
    add   CONSTRAINT FK_ClassStudentMap_ClassRoom FOREIGN KEY (ClassId)
    REFERENCES tbClassRoom(crId)
Drop a foreign key constraint in sql server

You can drop any foreign key constraint just by altering the child table, simply drop the foreign key constraint


ALTER TABLE ClassStudentMap
DROP CONSTRAINT FK_ClassStudentMap_ClassRoom;

To understand the relationship between foreign key and primary key, You should also look at SQL Primary Key

Hire SQL Developer
FOREIGN KEY Constraint in SQL Server
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
FOREIGN KEY Reference in SQL
MS SQL Examples | Join MS SQL Course