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:

Add foreign key using Create table statement
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:

Create the database in the system.
Create two tables in the same database.
View Table structure before foreign key addition.
Add a foreign key to the table.
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:

CREATE DATABASE Database_Name;

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

CREATE DATABASE Vehicles;

Step 2: Create two Tables in the database

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

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_1)
);

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

CREATE TABLE Cars_Details
(
Car_Number INT AUTO_INCREMENT PRIMARY KEY,
Model INT,
Cars_Name VARCHAR (20),
Colour VARCHAR (20),
);

The following query creates Cars_Price_Details table in the Vehicles database:

CREATE TABLE Cars_Price_Details
(
Model INT NOT NULL PRIMARY KEY,
Cars_Price INT NOT NULL
);

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:

DESC Cars
or
DESCRIBE Cars ;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	NO	PRI	NULL	auto_increment
Model	INT	Yes	-	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL
Color	Varchar(20)	Yes	-	NULL	-

DESC Cars_Price_Details;

Field	Type	NULL	Key	Default	Extra
Car_Model	INT	No	PRI	NULL	-
Car_Price	INT	No	-	NULL

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:

CREATE TABLE table_name1
(
Column_Name_1 data type (size of the column_1),
Column_Name_2 data type (size of the column_2),
......,
Column_Name_N data type (size of the column_N) FOREIGN KEY REFERENCES Table_Name2 (Column_Name)
) ;

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

CREATE TABLE Cars_Details
(
Car_Number INT AUTO_INCREMENT,
Model INT FOREIGN KEY REFERENCES Cars_Price_Details (Car_Model),
Cars_Name VARCHAR (20),
Color VARCHAR (20) UNIQUE,
Price INT NOT NULL
) ;

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 4th step, you have to type the following DESC command in SQL:

DESC Cars_Details;

Field	Type	NULL	Key	Default	Extra
Car_Number	INT	Yes	PRIMARY	NULL	auto_increment
Model	INT	No	FOREIGN	NULL	-
Car_Name	Varchar(20)	Yes	-	NULL	-
Color	Varchar(20)	Yes	-	NULL	-
Price	INT	NO	-	NULL	-
Average	INT	NO	-	0	-

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:

ALTER TABLE Table_Name1 ADD CONSTRAINT ForeignKey_Name FOREIGN KEY (Column_Name) REFERENCES Table_Name2 (Column_Name);

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

ALTER TABLE Cars_Details ADD CONSTRAINT FK_Cars_Details FOREIGN KEY (Model) REFERENCES Cars_Price_Details (Car_Model);

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:

ALTER TABLE Table_Name DROP FOREIGN KEY Foreign_Key_Name;

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

ALTER TABLE Cars DROP FOREIGN KEY FK_Cars_Details;

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz