MySQL Foreign Key

Summary: in this tutorial, you will learn about the MySQL Foreign Key constraint and how to create, drop and disable a foreign key constraint.

MySQL Foreign Key Introduction

A foreign key is a column (or set of columns) that establishes a relationship between data in two tables. The primary key column (or columns) of the first table is referenced by the column (or columns) of the second table in a foreign key reference. The foreign key is the column (or columns) of the second table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table. For example, look at the following two tables.

Table: Student

Table: Subject

The StudentID column in the Subject table links to the StudentID which is a primary key column in the Student table.

The StudentID column is the PRIMARY KEY in the Student table. The StudentID column is a FOREIGN KEY in the Subject table.

A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.

Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the primary key columns of the parent table, or values in these foreign key columns must be NULL.

For example, each row in the Student table has a StudentID that exists in the StudentID column of the Subject table. Multiple rows in the Subject table can have the same StudentID.

Self-referencing foreign key

The child and parent tables might sometimes refer to the same table. The foreign key in this scenario refers back to the primary key in the same table. Consider the following Student table.

The rollNumber column is a foreign key that refers to the StudentID column which is the primary key of the Student table.

The foreign key on the column rollNumber is known as a recursive or self-referencing foreign key.

MySQL FOREIGN KEY syntax

Following is the basic syntax of defining a foreign key constraint in CREATE TABLE and ALTER TABLE constraint in MySQL.

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (col_name, ...)
REFERENCES parent_tbl_name (col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

Let us understand the above syntax in detail.

• constraint_name – first, specify the name of the foreign key constraint after the CONSTRAINT. If we do not provide the name, MySQL automatically assigns a name for the foreign key.
• col_name – Second, specify the comma-separated column names after the FOREIGN KEY keywords. Here, also the name of the foreign key name is optional. If you do not provide it, MySQL assigns it on its own.
• parent_tbl_name – Third, specify the name of the parent table along with the comma-separated column lists to which the foreign key column references.
• reference_option – Finally, specify the referential integrity between the parent and the child table by using the ON DELETE and ON UPDATE clauses. The reference_option determines the action that MySQL will take when values in the parent key columns are deleted (ON DELETE) or updated (ON UPDATE).
• MySQL has five different referential options which are as below:
  1. CASCADE – if we delete or update any row from the parent table, the values of the matching rows in the child table will be deleted or updated automatically.
  2. SET NULL – if we delete or update any row from the parent table, the values of the foreign key columns in the child table are set to NULL.
  3. RESTRICT – if we delete or update any row from the parent table that has a matching row in the reference(child) table, MySQL does not allow us to delete or update rows in the parent table.
  4. NO ACTION – it is similar to RESTRICT. But it has one difference it checks referential integrity after trying to modify the table.
  5. SET DEFAULT – it is recognized by the MySQL parser. However, the InnoDB and NDB tables both rejected this action.

MySQL FOREIGN KEY examples

Let us understand the concept of the foreign key with the help of some examples. So first, create a database named mysqlfk and start using it with the below command:

mysql> CREATE DATABASE mysqlfk;
mysql> use mysqlfk;

Now, let’s create a Student table using the following query.

CREATE TABLE Student (
   StudentID int NOT NULL,
   FirstName varchar(50) NOT NULL,
   LastName varchar(50),
   Age int,
   PRIMARY KEY (StudentID)
);

In the above table, we have defined StudentID as a Primary key.

The following SQL creates a FOREIGN KEY on the StudentID column when the Subject table is created.

CREATE TABLE Subject (
   SubjectID int NOT NULL,
   CourseNumber int NOT NULL,
   StudentID int NOT NULL,
   PRIMARY KEY (SubjectID),
   FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Add Foreign Key using ALTER TABLE

The following SQL command adds a Foreign Key to an existing table.

ALTER TABLE Subject
ADD FOREIGN KEY (StudentID) REFERENCES Student(StudentID);

Drop foreign key MySQL

For dropping a Foreign key from a MySQL table, you need to know the name of the foreign key first. You can get the name of the foreign key by show create table command as follows.

SHOW CREATE TABLE Subject;

Output:

Now, you can drop the foreign key using the following command.

ALTER TABLE Subject
DROP FOREIGN KEY subject_ibfk_1;