How to Add Foreign Key in SQL

In this article, we will learn how to add a Foreign Key to the column in the table of our SQL database.

The FOREIGN KEY in SQL is used to join the record of two tables in the database. The column defined as the FOREIGN KEY in one table must be the PRIMARY KEY in another table in the same database.

We can easily add foreign key to the column in the following two ways:

1. Add foreign key using Create table statement
2. Add foreign key using Alter Table statement

If you want to add a FOREIGN KEY to the column into the SQL table, you have to follow the below steps in the given sequence:

1. Create the database in the system.
2. Create two tables in the same database.
3. View Table structure before foreign key addition.
4. Add a foreign key to the table.
5. View the table structure.

Now, we are going to explain the above steps with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

Suppose you want to create the Vehicles database. For this, you have to type the following command in Structured Query Language:

Step 2: Create two Tables in the database

Now, you have to use the following SQL syntax for creating the tables in your database:

The following SQL query creates the Cars_Details table in the Vehicles database.

The following query creates Cars_Price_Details table in the Vehicles database:

Step 3: View the Table Structure before Foreign key Addition

After table creation and data insertion, you can view the structure of both tables by typing the following query in your SQL application:

Step 4: Add a Foreign key to the column in the table

If you want to add the foreign key at the time of table creation, then you have to use the following CREATE TABLE syntax in SQL:

Example

The following query adds the FOREIGN KEY on the 'Model' column in the Cars_Details table:

This query in SQL joins the Cars_Details table with the Cars_Price_Details table with the help of a foreign key.

Step 5: View the Table Structure after Foreign key Addition

To check the result of the query executed in the 4^th step, you have to type the following DESC command in SQL:

As shown in the above output, the Model column is created as the foreign key.

Add Foreign key to the Existing Table

If you want to add the foreign key to the existing table, you have to use the following ALTER syntax in SQL:

The following query adds a FOREIGN KEY on the Model column when the Cars_Details table already exists in the database system:

This ALTER query in SQL joins the Cars_Details table with the Cars_Price_Details table with the help of a foreign key.

Delete foreign key from the table

If you want to delete the foreign key from the column of the table, you have to use the following ALTER syntax in SQL:

The following query deletes the created FOREIGN KEY from the Model column of the Cars_Details table: