Create Foreign Key in SQL Server – Complete tutorial

In this SQL Server tutorial, we will learn about Foreign Key in SQL Server, create foreign key in SQL server 2019, Different examples related to Foreign Key in SQL Server, and will cover the following topics.

• Foreign Key in SQL Server
• Create Foreign key in SQL Server using Query
• Create Foreign key in SQL Server Management Studio
• Add foreign key to Existing Column in SQL Server
• Disable foreign key in SQL server
• Truncate table with foreign key in SQL server
• Delete foreign key in SQL server
• Nullable foreign key in SQL server
• Drop foreign key in SQL server if exists
• Index on foreign key in SQL server

Foreign Key in SQL Server

Let us try to understand what is a foreign key in SQL server?

• In SQL Server, a foreign key is a column (or combination of columns) that allows us to create a referential relationship between two tables data.
• A foreign key is a field or column in one table that refers to the primary key in another table.
• The table which carries a foreign key is known as the child table. Whereas the table which carries the primary key is known as referenced or parent table.
• Furthermore, if a foreign key refers to another column in the same table, this is known as a self-reference.

So, in this section, we have learned about the foreign key in SQL Server, and why it is used. Now, let’s understand different ways to create a foreign key in a SQL Server table.

Here, I am using SQL server express edition.

Create Foreign key in SQL Server using Query

As discussed in the above section, a foreign key is used to create a referential relationship between two table’s data. So for demonstration, we are going to create two tables, first is the Customer table containing all the basic details of the customers of an organization. And second is the Orders table containing all the order details of a customer.

According to database design, the Orders table should not include any invalid customer data. So, we’ll need to create a referential relationship between the Customers and Orders table to solve this problem.

Now to create a Customer table and populate it with some data, we will use the following query given below.

CREATE TABLE Customer (
	id INT PRIMARY KEY,
	Name VARCHAR(50),
	gender VARCHAR(50),
	Country VARCHAR(50)
);
insert into Customer (id, Name, gender, Country) values (1, 'Caddric Senton', 'Male', 'New Zealand');
insert into Customer (id, Name, gender, Country) values (2, 'Morgen Wardall', 'Male', 'New Zealand');
insert into Customer (id, Name, gender, Country) values (3, 'Bertram Boeck', 'Female', 'Australia');
insert into Customer (id, Name, gender, Country) values (4, 'Noelyn Burrage', 'Female', 'Australia');

By using the above query, we are creating a customer table and defining the id column as PRIMARY KEY. After this, we are populating the table with some sample data.

And after executing the query successfully, we will get the following customer table.

Now to create referential relation between the Customer and Orders table, we will create a foreign key in the Orders table that will refer to the PRIMARY KEY column in the Customer table.

And for this implementation, we are going to execute the following query given below.

CREATE TABLE Orders(
   OrderID INT PRIMARY KEY,
   OrderDate DATE,
   CustomerID INT,
   Amount VARCHAR(50),
   CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES Customer(ID)
);

By implementing the above query, we are creating an order table with 4 columns. And we have also defined the FOREIGN KEY constraint for the CustomerID column that will refer to the ID column in the Customer table.

Now, while inserting a new row into the Orders table, the CustomerID value must match the values in the Customers table’s ID column.

insert into Orders (OrderID, OrderDate, CustomerID, Amount) values (101, '5/4/2021', 1, '$92.83');
insert into Orders (OrderID, OrderDate, CustomerID, Amount) values (102, '3/15/2021', 2, '$89.48');
insert into Orders (OrderID, OrderDate, CustomerID, Amount) values (103, '12/3/2020', 3, '$95.27');
insert into Orders (OrderID, OrderDate, CustomerID, Amount) values (104, '3/22/2021', 4, '$85.73');

So by using the FOREIGN key constraint, we can create a referential relationship between multiple tables easily. And in our example, we are creating a relationship between the ID column and CustomerID column in the Customer and Orders table respectively.

Create Foreign key in SQL Server Management Studio

To create a foreign key column in a SQL Server table, we can also use SQL Server Management Studio. A SQL Server Management Studio is a software application used to create and manage components in SQL Server.

Now for demonstration, consider the following parent and child table available in SQL Server.

Parent Table: Assume we already have a Parent table named “Customer” and it contains ID, name, gender, and country columns with “ID” serving as the primary key.

Child Table: For the child table, we are going to create an Orders table containing all the order details of a customer. For this implementation in SQL Server Management Studio, follow the following steps given below.

Step-1. Under Object Explorer, right-click the Tables directory in our database and select “New“>”Table..“. It will open a table designer page in SQL Server Management Studio.

Step-2. Now on the designer page, we are going to add the required columns like- OrderID, OrderDate, CustomerID, and Amount.

Step-3. After adding all the column’s details, we are going to add the foreign key relationship between the ID column in the Customer table and the CustomerID column in this child table.

Step-4. Now right-click the CustomerID column and click on the “Relationships…” option. It will open a “Foreign Key Relationship” window.

Step-5. In the new window, click on the “Add” option and it will add a new relationship. Now in the right side menu, click on the “…” icon under to “Tables and Columns Specification” option.

Step-6. Now in the “Tables and Columns” window, first select the primary key table as “Customer” and next select the ID column under the primary key table. After this, select the CustomerID column under the foreign key table and click OK.

Step-7. Now close the relationships window and click on the “SAVE” option to save the newly created table.

Add foreign key to Existing Column in SQL Server

Till now, we have understood different ways through which we can create a foreign key column while creating a new table. But what if we want to add a foreign key to an existing table in SQL Server.

So in this section, we will learn how to add a foreign key constraint to an existing table using Transact-SQL query. And for this implementation, we can follow the following syntax given below.

ALTER TABLE child_table
ADD CONSTRAINT foreign_key_name
    FOREIGN KEY(child_column1, ... child_column_n)
    REFERENCES parent_table(parent_column1, ... parent_column_n);

• In the above syntax, the child_table is used to represent the child table in which we want to add a foreign key.
• Next in the syntax, we have defined foreign_key_name which represents the constraint name.
• In the FOREIGN KEY statement, the child_column1, … child_column_n represents the columns that we want as a foreign key.
• Next, the parent_table is used to define the parent table name with which we want to create a referential relation. And parent_column1, … parent_column_n are used to define the primary key columns from the parent table.

Now to better understanding, consider the following example below, used to add the foreign key column to the Orders table.

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerCheck
    FOREIGN KEY(CustomerID)
    REFERENCES Customer(id);

In the example, we are using the ALTER TABLE command to modify the Orders table and add a foreign key into it. And we have defined the CustomerID column in the Orders table as a foreign key that will refer to the id column in the Customer table.

Disable foreign key in SQL server

Till now, we have learned how we can use a foreign key constraint in SQL Server. But we can also disable this foreign key constraint while inserting or updating table data. We can use this option when we already that the new data will not breach the existing constraint.

Now there are two methods through which we can disable the foreign key constraint, and we will discuss both with the help of an example.

Using Transact-SQL

To disable the foreign key using the Transact-SQL query, we can follow the following query given below.

ALTER TABLE child_table 
NOCHECK CONSTRAINT constraint_name;

In the above syntax, we are using the ALTER TABLE command to modify the table having a foreign key. After this, we have to define the constraint name in place of constraint_name which we want to disable.

Now for demonstration, consider the following example used to disable the foreign key constraint named FK_CustomerCheck from the Orders table.

ALTER TABLE Orders 
NOCHECK CONSTRAINT FK_CustomerCheck;

Using SQL Server Management Studio

Now to disable the foreign key constraint using SQL Server Management Studio, we have to follow the following given steps.

• First, go to Object Explorer and expand the table having a foreign key constraint.
• Next, expand the “Keys” folder, right-click the constraint and click on the “Modify” option.

• Now under “Table Designer“, first select the “Enforce Foreign Key Constraint” and set it to “No” from the drop-down list.

• In the end, close the window and save the changes made in the table.

Delete foreign key SQL server

There are two methods in SQL Server, through which we can delete a foreign key constraint. The first is by using the Transact-SQL query and the second is by using the SQL Server Management Studio.

Using Transact-SQL

Now to delete a foreign key constraint using Transact-SQL query, we can use the following syntax given below.

ALTER TABLE table_name   
DROP CONSTRAINT constraint_name; 

In the syntax, we have to define the name of the table which have the foreign key instead of table_name. After this, we have to use the constraint name in place of costraint_name to delete the foreign key constraint.

For demonstration, consider the following example given below, used to delete the foreign key constraint from the Orders table.

ALTER TABLE Orders  
DROP CONSTRAINT FK_CustomerCheck; 

After the successful execution of the above example, the foreign key constraint will be deleted, and we will get the following message.

Using SQL Server Management Studio

• First, go to Object Explorer and expand the table having a foreign key constraint.
• Next, expand the “Keys” folder, right-click the constraint and click on the “Delete” option.

• Next, in the “Delete Object” dialog box, click “OK” to delete the foreign key constraint.

Drop foreign key SQL server if exists

According to database design, it is always preferred to first check if an object exists or not before deleting it. And we can also check if a foreign key constraint exists or not before directly trying to delete it.

For this implementation, we can execute the following syntax given below.

IF (OBJECT_ID('dbo.FK_ConstraintName', 'F') IS NOT NULL)
BEGIN
    ALTER TABLE table_name 
    DROP CONSTRAINT FK_ConstraintName
END

In the above syntax, we are using the IF statement to check if a foreign key constraint exists or not. And if the constraint exists, then we are using the DROP CONSTRAINT statement to delete the foreign key constraint.

For example, consider the following query used to check if a foreign key constraint named “FK_Orders_Customer” exists or not. And if it exists then it will delete that constraint from the Orders table.

IF (OBJECT_ID('dbo.FK_Orders_Customer', 'F') IS NOT NULL)
BEGIN
    ALTER TABLE Orders 
    DROP CONSTRAINT FK_Orders_Customer
END

Now, if the above query is executed successfully, it will delete the “FK_Orders_Customer” constraint, and we will get the following message.

Nullable foreign key SQL server

In SQL Server, while creating a primary key column, it is always mandatory to set that field to non-nullable. Because a primary key field is used to represent unique records in a table. But while creating a foreign key column, we can set the column to be nullable as the foreign is used to represent the relationship among the table columns.

And for this implementation, we just need to use the NULL constraint while defining the foreign key column in a table.

Here is a simple example of creating an Orders table with a nullable foreign key.

CREATE TABLE Orders(
   OrderID INT PRIMARY KEY,
   OrderDate DATE,
   CustomerID INT NULL,
   Amount VARCHAR(50),
   CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES Customer(ID)
);

In the example, we are creating an Orders table with four columns. And we have defined the CustomerID column as a nullable foreign key referring to the id column in the Customer table.

Read: SQL Server Substring Function [9 Examples]

Index on foreign key SQL server

One of the most essential performance-tuning aspects in SQL Server is the index. They’re based on a table or view with a B-Tree structure that allows fast access to the desired data based on the index column’s values, which speeds up query execution.

If your table doesn’t have an index, the SQL Server Engine will scan all of the data in the table to identify the row that matches the desired data criteria. You might think of a table scan as reading all of the book pages in search of a specific term, with the book index assisting you in rapidly discovering the information you need. Here, the index in SQL Server is similar to a book index.

If we create a primary key in a table, a clustered index will be created on the constraint columns automatically by default. But in the case of a foreign key constraint on the child table, no index will be created on the constraint keys automatically.

And it is always recommended to create an index on each foreign key constraint. As it helps in better performance on maintaining the relationship on delete of a primary key. It also provides better performance while implementing join operation between parent and child table.

Now to create an index on a foreign key constraint in SQL Server, we can follow the below-given syntax.

CREATE INDEX index_name on child_table(foreign_key_columns ASC|DESC) 

Here is an example in which we are creating an index on a foreign key column named CustomerID from the Orders table.

CREATE INDEX FR_index on Orders(CustomerID ASC) 

Read SQL Server stored procedure output parameter

Truncate table with foreign key SQL server

A truncate statement in SQL Server is used to delete all the data in a table without deleting the columns of the table. This statement is used to empty the data without deleting the column of a table.

Now, if we try to truncate the child table having a foreign key column in it, all the data will be removed without any issue. But, if we try to truncate the parent table having primary then, the SQL Server will raise an error.

An ideal approach to implement this task is as follows:

1. Remove the constraint from the child table.
2. Now truncate the table as required.
3. In the end, again add the constarint if required.

Now to implement the above-discussed approach, we can follow the following given syntax.

--deleting the constraint
ALTER TABLE child_table  
DROP CONSTRAINT constraint_name
GO

--trunacting the parent table
TRUNCATE TABLE parent_table
GO

In the above syntax, first, we are deleting the foreign key constraint from the child table. After this, we are truncating the parent table, having a primary key.

Now for demonstration, consider the following example given below.

--deleting the constraint
ALTER TABLE Orders  
DROP CONSTRAINT FK_Orders_Customer
GO

--trunacting the parent table
TRUNCATE TABLE Customer
GO

In the example, we are deleting the “FK_Orders_Customer” constraint from the Orders table. After this, we are using a truncate statement to remove all the data from the parent table named Customer.

Note:- Remember to first add data to the parent table before creating the foreign key constraint.

You may like the following SQL Sever tutorials:

• Advanced Stored Procedure Examples in SQL Server
• MySQL vs SQL Server – Key Differences in Details
• SQL Server drop table if exists
• SQL Server convert integer to string
• Exception Handling in SQL Server
• SQL Server bulk insert from CSV file

So in this SQL Server tutorial, we have learned about Foreign Key in SQL Server, Different examples related to Foreign Key in SQL Server, and we have also covered the below topics.

• Foreign Key in SQL Server
• Create Foreign key in SQL Server using Query
• Create Foreign key in SQL Server Management Studio
• Add foreign key to Existing Column in SQL Server
• Disable foreign key in SQL server
• Truncate table with foreign key in SQL server
• Delete foreign key in SQL server
• Nullable foreign key in SQL server
• Drop foreign key in SQL server if exists
• Index on foreign key in SQL server