What is foreign key in MySQL?

Foreign Key

A foreign key is a column or a combination of multiple columns of one table (we can call it a child table) that has a reference to the primary key of another table (we can call it a parent table).

I have discussed about primary key and other indexes in my previous article at: https://webtechbased.com/what-is-indexing-in-mysql/

The parent table holds the primary key and the child table holds the foreign key.

Through the foreign key, we maintain the reference of the related data between the tables.

Condition

The storage engine (like InnoDB, MyISAM, etc) must be the same for both parent and child tables.
Both foreign key and primary key columns must have similar data types (like int, char). The length of string types (char, varchar) may differ.

Before creating a foreign key we need to create a primary key first.

Create primary key first

To understand the foreign key with an example we first create the parent table. For this, we will create the user table and add the primary key to its user_id column when creating the user table.

CREATE TABLE users (
    user_id int NOT NULL,
    email varchar(255) NOT NULL,    
    first_name varchar(255) NOT NULL,
    last_name varchar(255),    
    PRIMARY KEY (user_id)
);

Create foreign key

Now let’s say the child table is user_images and its columns are image_id, user_id, image_name, and image_url. Here we will add a foreign key to the user_id column of the user_images table.

A foreign key can be added while creating the table and can be added after creating the table.

Create foreign key when creating a table

We can create a foreign key when creating a table using the following syntax.

CREATE TABLE user_images (
    image_id int NOT NULL,
    user_id int NOT NULL,
    image_name varchar(255) NOT NULL,
    image_url varchar(255) NOT NULL,
    PRIMARY KEY (image_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

But if we create a foreign key in this way, we cannot give the name of the foreign key. Creating a foreign key with the name will help us while deleting the foreign key and we will be able to see it when we delete the foreign key. To give a name to the foreign key we can use the following SQL syntax.

CREATE TABLE user_images (
    image_id int NOT NULL,
    user_id int NOT NULL,
    image_name varchar(255) NOT NULL,
    image_url varchar(255) NOT NULL,
    PRIMARY KEY (image_id),
    CONSTRAINT fk_user_images FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Create foreign key on an existing table

If we had not added a foreign key while creating the user_images table, that is if we had used the following syntax:

CREATE TABLE user_images (
    image_id int NOT NULL,
    user_id int NOT NULL,
    image_name varchar(255) NOT NULL,
    image_url varchar(255) NOT NULL,
    PRIMARY KEY (image_id)
);

then we could add a foreign key using the following SQL syntax:

ALTER TABLE user_images ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

or we can use the following syntax to add a name to the foreign key.

ALTER TABLE user_images ADD CONSTRAINT fk_user_images FOREIGN KEY (user_id) REFERENCES users(user_id);

Foreign key for Database operation

Common database operations are create, read, update and delete.
In MySQL, insert is used to create data, select is used to read data, update is used to update data, and delete is used to delete data. Now we will explain how foreign key works in create, update, and delete database operations with examples.

Foreign key for create

To understand how the foreign key works for insert (create), we will first add test data to the users table.

INSERT INTO `users` (`user_id`, `email`, `first_name`, `last_name`) VALUES ('1', 'dev@dev.com', 'joe', 'clark');

We have created a new record in the users table with user_id = 1.

Now we will add test data to the user_images table.

INSERT INTO `user_images` (`image_id`, `user_id`, `image_name`, `image_url`) VALUES ('1', '1', 'nature', 'http://someurl.com');

This will work. Now the question is, will the below SQL syntax work?

INSERT INTO `user_images` (`image_id`, `user_id`, `image_name`, `image_url`) VALUES ('2', '2', 'nature', 'http://someurl.com');

The answer is no. MySQL throws the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (products.user_images, CONSTRAINT fk_user_images FOREIGN KEY (user_id) REFERENCES users (user_id))

Since we have not created any record in the users table with user_id = 2, so when we try to add a record in the user_images table with user_id = 2 we will get an error because we have already added a foreign key in the user_images table for user_id column.

Foreign key for update

Similarly, in the case of an update, if we use the following SQL syntax, then MySQL will throw an error.

update user_images set user_id = '2' where image_id = 1;

This will give the following error.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`products`.`user_images`, CONSTRAINT `fk_user_images` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))

We added a record to the users table with user_id = 1. Now if we want to update this table using the following syntax then what will happen?

UPDATE users SET user_id = 2 WHERE user_id = 1;

This will give the following error.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`products`.`user_images`, CONSTRAINT `fk_user_images` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))

Here MySQL is giving an error because a record is already added in the user_images table with user_id =1 and user_id is the foreign key of this table.

Foreign key for delete

Similarly, if we want to delete a record from the users table (parent table) using the following SQL syntax, then MYSQL will throw an error.

DELETE FROM users WHERE user_id = '1';

This will give the following error.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`products`.`user_images`, CONSTRAINT `fk_user_images` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))

We see from the database operations above that the foreign key identifies each row of the parent table uniquely and maintains referential integrity.

Now the question is how do we update or delete the primary key of the user table (parent table)?

For this, we can use CASCADE, SET NULL, SET DEFAULT, etc with ON UPDATE or ON DELETE syntax as per the requirements while creating a foreign key.

Use of Cascade

If we want to update the user_id in the user_images table when we update the user_id in the users table, then we will use ON UPDATE CASCADE when creating the foreign key.

Again, during deletion, if we want to delete a record of a user_id in the users table and also want to delete all the records of that user_id from the user_images table with this, then we will use ON DELETE CASCADE when creating the foreign key.

As the record of the related table will be deleted in the above case, the above process is very risky.

Since we have already created a foreign key for the user_image table, we will first delete it and then re-create it.

At the end of this article, we have explained how we can drop a foreign key.

We will use the following SQL syntax for this.

ALTER TABLE user_images DROP FOREIGN KEY fk_user_images;

We will use the following SQL to re-create the foreign key. Here we will use cascade when creating the foreign key.

ALTER TABLE user_images ADD CONSTRAINT fk_user_images FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE;

If we create a foreign key while creating a table, then we can also use ON UPDATE or ON DELETE like this.

Now we can update the primary key (user_id) of the users table (parent table) and delete records.

Before updating we will see the output of existing data of users and user_images table using select query.

To fetch records from the users table we can use the syntax below:

SELECT * FROM users;

The output is:

+---------+-------------+------------+-----------+
| user_id | email       | first_name | last_name |
+---------+-------------+------------+-----------+
|       1 | dev@dev.com | joe        | clark     |
+---------+-------------+------------+-----------+

Now if we update using the following SQL syntax:

UPDATE users SET user_id = '2' WHERE user_id = '1';

We will not get any error here. The syntax above updates the user_id from 1 to 2 of both users and user_images tables .

We are using the following syntax to fetch records from the users table

SELECT * FROM users;

and the output is:

+---------+-------------+------------+-----------+
| user_id | email       | first_name | last_name |
+---------+-------------+------------+-----------+
|       2 | dev@dev.com | joe        | clark     |
+---------+-------------+------------+-----------+

We are using the following SQL syntax to fetch records from the user_images table

SELECT * FROM user_images;

and the output is:

+----------+---------+------------+-----------+
| image_id | user_id | image_name | image_url |
+----------+---------+------------+-----------+
|        1 |       2 | nature     | test      |
+----------+---------+------------+-----------+

We can see from the results that the user-id of these two tables has changed from 1 to 2.

Now if we want to delete a record from the users table (parent table) using the following SQL syntax, then MySQL will not throw any error.

DELETE FROM users WHERE user_id = '2';

The above query deletes the records for user id = 2 in the users table and all the records for user_id = 2 in the user_images table and we can see that from the output of the select query.

The following two SQL SELECT syntax will return an empty set.

SELECT * FROM users;

This query returns an empty set and the query below

SELECT * FROM user_images;

also returns an empty set.

How to Drop foreign key in MySQL?

We created this foreign key with the name fk_user_images. We can use that name while dropping the foreign key. We can use the following SQL to drop the foreign key.

ALTER TABLE user_images DROP FOREIGN KEY fk_user_images;

We can learn more about foreign keys at: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Table of Contents

Foreign Key

Condition

Create primary key first

Create foreign key

Create foreign key when creating a table

Create foreign key on an existing table

Foreign key for Database operation

Foreign key for create

Foreign key for update

Foreign key for delete

Use of Cascade

How to Drop foreign key in MySQL?