SQL FOREIGN KEY

In this article, we will learn about the FOREIGN KEY constraints and how to define a FOREIGN KEY constraint to build the relationship between two tables.

In a Relational Databases Management System (RDBMS), a FOREIGN KEY is a field or bunch of fields which is used to build a relationship between two tables or join the two tables.

• FOREIGN KEY is also known as referencing key in the RDBMS
• Using FOREIGN KEY constraints between two tables, we define a parent-child relationship between two tables.
• The field defines the PRIMARY KEY constraints in a table known as the parent table, while the same field defines the FOREIGN KEY constraints in another table is known as the child table.
• There can be only one PRIMARY KEY in the table, but there is no defined limit on the FOREIGN KEY. We can have one or more than one FOREIGN KEY on the table.
• We can define FOREIGN KEY constraints while creating the table or define FOREIGN KEY constraints on the already existing table.

Let’s take an example to understand and learn about the FOREIGN KEY constraints.

Example:

Here, we have two tables one is the Course table and the second is the Student table:

The students select courses. The following FOREIGN KEY constraints example is defined on one field.

Table Number One: Student Table

Student_Id	First_Name	Last_Name	Email_Id	City
101	Kareena	Sayyed	ksayyed01@gmail.com	Hyderabad
102	Anmol	Temani	anmolt23@gmail.com	Jalgaon
103	Harshal	Patel	patel02h@gmail.com	Mumbai
104	Sakshi	Hood	sakshihood10@gmail.com	Pune
105	Suresh	Kohli	kohlisuresh1@gmail.com	Hyderabad

Table Number Two: Course Table

Course_Id	Course_Name	Student_Id
C101	Java	101
C102	SQL	102
C103	SOA	Null
C104	SAP	104
C105	MuleSoft	103
C101	Java	105

In the Student table, the Student_Id field is the PRIMARY KEY and in the Course table, Student_Id is the FOREIGN KEY.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column because it has to be one of the values contained in the parent table.

Define FOREIGN KEY Constraints on CREATE TABLE

The following example defines a FOREIGN KEY on the Course table.

First, we have created the Student table with the following fields:

CREATE TABLE Student( Student_Id int NOT NULL, First_Name varchar(40) NOT NULL, Last_Name varchar(40) NOT NULL, Email_Id varchar(50),  City varchar(20) NOT NULL, PRIMARY KEY(Student_Id));

The following example shows how to define the FOREIGN KEY constraints on the other table.

Table Name Course:     

CREATE TABLE Course( Course_Id int NOT NULL, Course_Name varchar(40) NOT NULL, Student_Id int, FOREIGN KEY(Student_Id) REFERENCES Student(Student_Id));

In the Course table, we didn’t define any PRIMARY KEY. Just define the FOREIGN KEY constraints on the Student_Id.

The following FOREIGN KEY constraints example is defined on multiple fields.

Suppose we have three tables, one is the Package table, the second is the Data table, and the third is the Talk_time table:

Table 1: Package:

Package_Id	Data_Id	Talktime_Id
P1001	D1003	T1001
P1002	D1001	T1002
P1003	D1002	T1003
P1004	D1003	T1004
P1005	D1004	T1005

Table 2: Data:

Data_Id	Data_Limit	Data_Price
D1001	5	120
D1002	3	75
D1003	6	150
D1004	10	240
D1005	15	320

Table 3: Talk_Time:

Talk_Time_Id	Talk_Time _Limit	Talk_Time _Price
T1001	120	130
T1002	70	105
T1003	60	90
T1004	200	220
T1005	150	170

In the Talk_Time table, Talk_Time_Id is the PRIMARY KEY.

In the Data table, Data_Id is the PRIMARY KEY.

Whereas in the Package table, Talk_Time_Id and Data_Id are the FOREIGN keys

Table Number one: Data:

CREATE TABLE Data(Data_Id varchar(5) NOT NULL, Data_Limit int, Data_Price int, PRIMARY KEY(Data_Id));

Table Number two: Talk_Time:

CREATE TABLE Talk_Time(Talk_Time_Id varchar(5) NOT NULL, Talk_Time_Limit int, Talk_Time_Price int , PRIMARY KEY(Talk_Time_Id));

Table Number three: Package:

CREATE TABLE Package(Package_Id varchar(5) NOT NULL, Data_Id varchar(5), Talk_Time_Id varchar(5), FOREIGN KEY(Data_Id) REFERENCES Data(Data_Id), FOREIGN KEY(Talk_Time_Id) REFERENCES Talk_Time(Talk_Time_Id));

FOREIGN KEY Constraints using ALTER TABLE:

Suppose we have already created the table and want to define the FOREIGN KEY constraints on the field. We will use the ALTER TABLE query to add FOREIGN KEY constraints in such a case.

The follow query is used to add FOREIGN KEY constraints on the Student_Id field.

ALTER TABLE Course ADD FOREIGN KEY(Student_Id) REFERENCES Student(Student_Id);

ADD Keyword is used after the table name to add the FOREIGN KEY constraints to the already existing table.

DROP FOREIGN KEY constraint from the table

Use the following query to remove the FOREIGN KEY constraint from the table.

ALTER TABLE Course DROP FOREIGN KEY course_ibfk_1;

Drop keyword is used to remove FOREIGN KEY constraints from the Student_Id field.

course_ibfk_1 is the foreign key constraint name.

We can have null values in the FOREIGN KEY constraints field. We can have duplicate values in the FOREIGN KEY constraints field.