Foreign keys: MySQL | Himanshu Sharma

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCreate foreign keys between tables and choose the right ON DELETE and ON UPDATE action.

Keeping links honest

In the shop, orders.customer_id should always match a real customer. Without protection, a bug or a hasty DELETE could leave an order that points to a customer who no longer exists, an orphan. A foreign key constraint prevents that. It declares that a column in one table (the child) must match a primary or unique key value in another (the parent), or be NULL.

Creating foreign keys

Build a small publishing database. First the parent, then the child that references it:

sql

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS articles;
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL
);
CREATE TABLE articles (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  author_id INT NOT NULL,
  title     VARCHAR(120) NOT NULL,
  CONSTRAINT fk_articles_author FOREIGN KEY (author_id) REFERENCES authors (id)
);
INSERT INTO authors (name) VALUES ('Priya'), ('Kabir');
INSERT INTO articles (author_id, title) VALUES (1, 'Intro to SQL'), (1, 'Joins explained'), (2, 'Indexes');

The clause FOREIGN KEY (author_id) REFERENCES authors (id) is the whole idea. Naming it (fk_articles_author) makes errors and later changes easier to read. The child column and the parent column must have the same type (including UNSIGNED), and the parent column must be a key. MySQL automatically creates an index on the child column.

Now MySQL guards the relationship in both directions. It refuses a child with no parent:

sql

INSERT INTO articles (author_id, title) VALUES (99, 'Ghost writer');

result

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`articles`, CONSTRAINT `fk_articles_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

And by default it refuses to delete a parent that still has children:

sql

DELETE FROM authors WHERE id = 1;

result

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`articles`, CONSTRAINT `fk_articles_author` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`))

The default behaviour, RESTRICT (or NO ACTION), says "stop". Often that is exactly right, because it forces you to decide what should happen to the articles.

ON DELETE and ON UPDATE actions

You can choose a different reaction when you declare the key:

Action	When the parent row is deleted or its key changes
`RESTRICT` / `NO ACTION`	the change is refused (the default)
`CASCADE`	the change is copied to the children: they are deleted, or their key is updated
`SET NULL`	the child column becomes NULL (the column must allow NULL)
`SET DEFAULT`	not supported by InnoDB

An example of each: comments disappear with their article, while articles keep living when an editor is removed.

sql

DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS editors;
CREATE TABLE editors (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL);
ALTER TABLE articles ADD COLUMN editor_id INT NULL;
ALTER TABLE articles ADD CONSTRAINT fk_articles_editor FOREIGN KEY (editor_id) REFERENCES editors (id) ON DELETE SET NULL ON UPDATE CASCADE;
CREATE TABLE comments (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  article_id INT NOT NULL,
  body       VARCHAR(200) NOT NULL,
  CONSTRAINT fk_comments_article FOREIGN KEY (article_id) REFERENCES articles (id) ON DELETE CASCADE
);
INSERT INTO editors (name) VALUES ('Meena');
UPDATE articles SET editor_id = 1 WHERE id IN (1, 3);
INSERT INTO comments (article_id, body) VALUES (3, 'Great read'), (3, 'Thanks'), (1, 'Useful');

Delete the editor. The articles stay, with the editor link cleared:

sql

DELETE FROM editors WHERE id = 1;
SELECT id, title, editor_id FROM articles ORDER BY id;

result

+----+-----------------+-----------+
| id | title           | editor_id |
+----+-----------------+-----------+
|  1 | Intro to SQL    |      NULL |
|  2 | Joins explained |      NULL |
|  3 | Indexes         |      NULL |
+----+-----------------+-----------+

Delete an article. Its comments go with it:

sql

DELETE FROM articles WHERE id = 3;
SELECT id, article_id, body FROM comments ORDER BY id;

result

+----+------------+--------+
| id | article_id | body   |
+----+------------+--------+
|  3 |          1 | Useful |
+----+------------+--------+

Comment 3 stays because its article still exists, but the two comments on article 3 were removed by CASCADE.

Use CASCADE with care

CASCADE is convenient for data that cannot exist alone, like comments or order lines. It is dangerous when a long chain of cascades can wipe out a large part of the database from one delete. Many teams prefer RESTRICT for important parents (customers, products) and handle clean-up deliberately. Another popular alternative is a soft delete, where the parent is flagged as deleted instead of removed.

Adding and removing foreign keys

You can add a key to an existing table, as above, as long as the existing data already obeys it. To remove one, use its constraint name:

sql

ALTER TABLE comments DROP FOREIGN KEY fk_comments_article;
SHOW CREATE TABLE comments\G

result

*************************** 1. row ***************************
       Table: comments
Create Table: CREATE TABLE `comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `article_id` int NOT NULL,
  `body` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_comments_article` (`article_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Notice that dropping the foreign key leaves behind the index (KEY fk_comments_article) that MySQL created for it. Remove that separately with DROP INDEX if you no longer need it.

Looking at the relationships

The shop database has foreign keys on orders, order_items and products. Ask MySQL to list them from its metadata:

sql

SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop' AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;

result

+-------------+-------------+-----------------------+------------------------+
| TABLE_NAME  | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------+-------------+-----------------------+------------------------+
| order_items | order_id    | orders                | id                     |
| order_items | product_id  | products              | id                     |
| orders      | customer_id | customers             | id                     |
| products    | category_id | categories            | id                     |
+-------------+-------------+-----------------------+------------------------+

Practical notes

Loading lots of data in a strange order? You can switch checks off for your session with SET FOREIGN_KEY_CHECKS = 0; and back on with = 1. Use it only in controlled imports, since it lets bad data in.
A foreign key adds a small cost to inserts and deletes, because MySQL checks the parent. It is almost always worth it.
A self-referencing key is allowed, as with employees.manager_id pointing at employees.id.
Foreign keys cannot cross database servers, and they do not work on tables that use a different storage engine from InnoDB.

Common mistakes

Mismatched types such as INT against INT UNSIGNED or BIGINT, giving "Cannot add foreign key constraint".
Creating the child table before the parent.
Using SET NULL on a NOT NULL column.
Overusing CASCADE on important data.
Disabling foreign key checks and forgetting to turn them back on.

Exercise

Create departments_demo and staff_demo tables where each staff member belongs to a department. Make deleting a department that still has staff impossible. Insert data, try to delete a used department, and then delete a department with no staff.

Show one possible answer

sql

DROP TABLE IF EXISTS staff_demo;
DROP TABLE IF EXISTS departments_demo;
CREATE TABLE departments_demo (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL);
CREATE TABLE staff_demo (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments_demo (id) ON DELETE RESTRICT
);
INSERT INTO departments_demo (name) VALUES ('Design'), ('Support');
INSERT INTO staff_demo (name, department_id) VALUES ('Lena', 1);

sql

DELETE FROM departments_demo WHERE id = 1;

result

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`staff_demo`, CONSTRAINT `staff_demo_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments_demo` (`id`) ON DELETE RESTRICT)

sql

DELETE FROM departments_demo WHERE id = 2;
SELECT * FROM departments_demo;

result

+----+--------+
| id | name   |
+----+--------+
|  1 | Design |
+----+--------+

Key points

A foreign key makes a column in a child table point to the primary key of a parent table, and MySQL refuses values with no parent.
ON DELETE decides what happens to children when a parent is deleted: RESTRICT (block), CASCADE (delete children), SET NULL (clear the link).
Both columns need compatible types, and the parent column must be indexed (a primary or unique key). Foreign keys need InnoDB.
Foreign keys protect integrity but they are not a substitute for a good design, and CASCADE should be used with care.

Keeping links honest

Creating foreign keys

ON DELETE and ON UPDATE actions

Use CASCADE with care

Adding and removing foreign keys

Looking at the relationships

Practical notes

Common mistakes

Exercise

Key points

Check yourself