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

Table Number Two: Course Table

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:

Table 2: Data:

Table 3: Talk_Time:

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.