How To

How to set Foreign Key in SQL Server

CIP Official December 1, 2020

Foreign Key in SQL: A foreign key is a column attribute which is used to link multiple tables in a database. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. [1]

In this very quick tutorial, we will learn two most important methods to set a foreign key in MS SQL Server.

Tools Required:

  1. MS SQL Server 2008 or later version.
  2. MS SQL Server Management Studio. Download them from THIS LINK.

Method One:

  1. Open the MS SQL Management Studio and go to your database.
  2. For this specific example, lets assume that we have two tables in our database, Students and Classes. Each student can belong to a class and each class can have zero or many students.
  3. To implement this scenario, we will link the primary key of Classes table as foreign key in Students table. Lets say id column of Class table is primary key, then we must have to define a relevant column in our Students table like class_id (You can name the field as per your requirement).
Foreign Key in SQL

Now in order to link these two columns, goto your object explorer -> Databases -> Your Database -> Tables-> tblStudents. Now Right Click on Keys and click on New Foreign Key as shown in image below.

Foreign Key

A small window will appear showing all foreign key relationships of the table. Now select Tables and Column Specifications and click on small button right in front of it, as shown in image below.

Click on the Highlighted Button

From next window, Select your primary key table from the left dropdown menu. In our case, the primary key table is Classes table.

Select Primary key table

Now select the primary key from the dropdown underneath the primary key table and foreign key column from the dropdown underneath the foreign key table name.

Select primary and foreign key

Now click on OK button and press Ctrl+S to save and Done.

Method Two:

You can also set a column as a foreign key by using query while creating your table like,

CREATE TABLE Student(
    id int NOT NULL,
    class_id int NOT NULL,
    Name varchar(50),
    FatherName varchar(50),
    PRIMARY KEY (id),
    FOREIGN KEY (class_id) REFERENCES Class(id)
);

The above query will create a table called Student and set the class_id column as a foreign key.

I hope you enjoyed this tutorial. Thanks for Visiting C# Ui Academy.