Everything about Foreign Key in MySQL [with Examples]

Summary: In this tutorial, we will learn what a Foreign Key is in RDBMS and how can we create Foreign keys in MySQL. In addition, we will also learn to add and remove Foreign Key from a table in MySQL.

What is a Foreign Key?

Foreign Key is a column (attribute) or a set of columns that refer to the candidate key of another table.

Example:

In the above example ‘programming.roll_no‘ is the foreign key as it is referring to the ‘students.roll_no‘ which is a primary key (i.e candidate key).

Some of the prominent properties of Foreign Key in MySQL are

• Foreign Key is a constraint that restricts a column to only allow those entries which are present in the column to which it is referring (i.e primary key of another table).
• A table in MySQL can have more than one Foreign Key.
• Foreign Key establishes a “one to many” relationship between tables (In the above example one student can learn multiple programming languages).
• Foreign keys can only refer to candidate keys.

Also, it is important to remember that the table in which the Foreign key is defined is known as the child table and the table to which it is referring is known as the parent table.

Why Foreign Key can Only Refer to the Candidate key?

Because the candidate key has unique column values. In case, if Foreign Key refers to the column of another table having duplicate values then ambiguity will arise. A foreign key value is referring to which record of the referring table will be ambiguous.

Recommended: Table in MySQL

Create a Table with a Foreign Key

The general syntax to create a table with a foreign in MySQL is

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   [CONSTRAINT constraint_name]
   FOREIGN KEY (column_name, ...)
   REFERENCES parent_table(colunm_name,...)
   [ON DELETE referential_action]
   [ON UPDATE referential_action]
);

Note: Syntax within square brackets [] are optional.

where,

• IF NOT EXISTS ensures that table would only be created if it doesn’t already exist in the MySQL database.
• CONSTRAINT constraint_name is used to give a name to the Foreign Key constraint. If we exclude it then MySQL automatically generates a name for the constraint.
• ON UPDATE and ON DELETE are subclauses of the FOREIGN KEY clause which defines what referential action should take place in case of an UPDATE or DELETE operation on a key value in the parent table that has matching rows in the child table.
• referential_action could include CASCADE, SET NULL, RESTRICT, NO OPTION, and SET DEFAULT.

If we exclude all the optional syntax then the simplified statement to create a table with a Foreign Key constraint would be

CREATE TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   FOREIGN KEY (column_name, ...)
   REFERENCES parent_table(colunm_name,...)
);

Let’s see some examples for a better understanding.

Recommended: Primary key in MySQL

Example 1: Table with the single column as a Foreign Key.

CREATE TABLE students (
roll_no int PRIMARY KEY, 
name varchar(30) NOT NULL
);

CREATE TABLE programming (
roll_no int, 
language varchar(30), 
CONSTRAINT fk_student_roll FOREIGN KEY (roll_no) REFERENCES students(roll_no)
);

In this example, we have created a foreign key named ‘fk_student_roll’ in which the ‘roll_no’ column of the ‘programming’ table is referring to the ‘roll_no’ column of the ‘students’ table.

Hence ‘students’ is the parent table and ‘programming’ is the child table.

Example 2: Table with multiple columns as a Foreign Key.

CREATE TABLE customers (
id int, name varchar(30), 
address varchar(150), 
PRIMARY KEY (id, name)
);

CREATE TABLE orders (
order_id int PRIMARY KEY,
cust_id int, 
cust_name varchar(30),
cart_id int,
CONSTRAINT fk_customer_id_name FOREIGN KEY (cust_id, cust_name) REFERENCES customers(id, name)
);

In this example, we have defined a set of columns as the Foreign Key in which ‘{cust_id, cust_name}’ of the ‘orders’ table is referencing the ‘{id, name}’ of the customer’s table.

Notice, that the attributes in the ‘customers’ table to which the table ‘orders’ is referring (i.e {id, name}) have been defined as the primary key.

Because Foreign Key requires referencing columns to be indexed. The primary key constraint in MySQL automatically creates indexes for respective columns of the particular table.

Otherwise, If we try referencing the non-primary key columns during the process of adding a Foreign Key constraint then we will get the following error ‘Failed to add the foreign key constraint. Missing index for constraint‘.

Add a Foreign Key constraint to an Existing Table

The general syntax to add a Foreign Key constraint to an already existing table in MySQL is

ALTER TABLE table_name
   ADD [CONSTRAINT constraint_name]
   FOREIGN KEY (column_name, ...)
   REFERENCES parent_table(colunm_name,...)
   [ON DELETE referential_action]
   [ON UPDATE referential_action]
;

where syntax within square brackets is optional and has the same meaning as described before.

If we simplify the statement by excluding the optional syntax, then the statement to add a Foreign Key constraint to an already existing table would be

ALTER TABLE table_name
   ADD FOREIGN KEY (column_name, ...)
   REFERENCES parent_table(colunm_name,...)
;

Let’s see some examples for a better understanding.

Example 1: Adding a single attribute Foreign Key constraint.

CREATE TABLE students (
roll_no int PRIMARY KEY, 
name varchar(30) NOT NULL
);

CREATE TABLE programming (
roll_no int, 
language varchar(30) 
);

ALTER TABLE programming
ADD CONSTRAINT fk_student_roll 
FOREIGN KEY (roll_no)
REFERENCES students(roll_no)
;

In this example we first created a parent and child table (i.e ‘students’ and ‘programming’ table) then we explicitly added the foreign key constraint using MySQL ALTER TABLE statement.

Example 2: Adding a multiple attribute Foreign Key constraint.

CREATE TABLE customers (
id int, name varchar(30), 
address varchar(150), 
PRIMARY KEY (id, name)
);

CREATE TABLE orders (
order_id int PRIMARY KEY,
cust_id int, 
cust_name varchar(30),
cart_id int
);

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id_name 
FOREIGN KEY (cust_id, cust_name)
REFERENCES customers(id, name)
;

In this example again we had explicitly defined the foreign key constraint in which ‘customers’ is the parent table and ‘orders’ is the child table.

Remove or Drop Foreign Key Constraint

The general syntax to remove or drop foreign key constraints from an existing table in MySQL is

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

where ‘constraint_name‘ is the name of the foreign key constraint for the table ‘table_name‘.

In case you didn’t specify any name to the constraint but MySQL generated it automatically then use the following syntax to obtain the generated foreign key constraint name in MySQL.

SHOW CREATE TABLE table_name;

Example:

ALTER TABLE programming
DROP FOREIGN KEY fk_student_roll;

Hope by now you have got some ideas about the Foreign Key in MySQL.

There are other concepts left out like what would happen ON DELETE or ON UPDATE of records in the parent and child table? Significance of different referential options such as CASCADE, RESTRICT, SET DEFAULT, etc.