MySQL FOREIGN KEY: Referential Integrity and Syntax

A foreign key (FK) makes sure that a value in one table exists in another table. It protects the link between a child row and its parent. This property is called referential integrity.

query.sql

SQL

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
);

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    total DECIMAL(8,2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

INSERT INTO customers (name) VALUES ('Maya'), ('Rohan');
INSERT INTO orders (customer_id, total) VALUES (1, 100.00), (1, 50.00), (2, 70.00);

What it enforces

1. A child cannot point to a missing parent (error 1452):

query.sql

SQL

INSERT INTO orders (customer_id, total) VALUES (99, 10.00);

Output

OUTPUT

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

2. A parent cannot be deleted while children exist (error 1451, with RESTRICT):

query.sql

SQL

DELETE FROM customers WHERE id = 1;

Output

OUTPUT

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learn`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE)

3. With ON UPDATE CASCADE, changing the parent key updates the children:

query.sql

SQL

1
2

UPDATE customers SET id = 10 WHERE id = 2;
SELECT id, customer_id FROM orders ORDER BY id;

Output

OUTPUT

+----+-------------+
| id | customer_id |
+----+-------------+
|  1 |           1 |
|  2 |           1 |
|  3 |          10 |
+----+-------------+
3 rows in set

Order 3 now points to customer 10. (Changing keys is a bad idea, so this is shown only to explain the rule.)

The syntax

Text

PLAIN

1
2
3

[CONSTRAINT name] FOREIGN KEY (child_column, ...)
    REFERENCES parent_table (parent_column, ...)
    [ON DELETE action] [ON UPDATE action]

Name the constraint (fk_orders_customer).
The columns in the child and the parent must have the same type: same integer size, same UNSIGNED setting, same character set and collation for text.
The parent column must be a primary key or a unique column (a candidate key).

It needs the same type

query.sql

SQL

CREATE TABLE payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

Output

OUTPUT

ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'payments_ibfk_1' are incompatible.

BIGINT is not the same as INT UNSIGNED. Make them identical.

Only InnoDB

Foreign keys work with InnoDB tables. MyISAM accepts the syntax and silently ignores it. Use InnoDB (InnoDB).

The index requirement

InnoDB needs an index on the child column. If there is none, it creates one for you:

query.sql

SQL

SELECT index_name, column_name FROM information_schema.statistics WHERE table_schema = 'learn' AND table_name = 'orders' ORDER BY index_name;

Output

OUTPUT

+--------------------+-------------+
| INDEX_NAME         | COLUMN_NAME |
+--------------------+-------------+
| fk_orders_customer | customer_id |
| PRIMARY            | id          |
+--------------------+-------------+
2 rows in set

MySQL 8.4: foreign keys must reference a unique key

Older MySQL versions allowed a foreign key to reference a non-unique index, which is a non-standard extension. MySQL 8.4 refuses it by default (the setting restrict_fk_on_non_standard_key is ON):

query.sql

SQL

1
2

CREATE TABLE parent_codes (id INT PRIMARY KEY, code VARCHAR(5), KEY idx_code (code));
CREATE TABLE child_items (id INT PRIMARY KEY, parent_code VARCHAR(5), FOREIGN KEY (parent_code) REFERENCES parent_codes (code));

Output

OUTPUT

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'child_items_ibfk_1' in the referenced table 'parent_codes'

(This output is from MySQL 8.4. MySQL 8.0 accepts the statement.) If you migrate from an old schema that did this, add a UNIQUE index on the parent column.

Add a foreign key to an existing table

Existing data must already be valid:

query.sql

SQL

CREATE TABLE tickets (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT UNSIGNED NOT NULL);
INSERT INTO tickets (customer_id) VALUES (1), (10), (77);

ALTER TABLE tickets ADD CONSTRAINT fk_tickets_customer FOREIGN KEY (customer_id) REFERENCES customers (id);

Output

OUTPUT

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`#sql-temp`, CONSTRAINT `fk_tickets_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Find the orphans and deal with them first:

query.sql

SQL

SELECT t.id, t.customer_id FROM tickets t LEFT JOIN customers c ON c.id = t.customer_id WHERE c.id IS NULL;
DELETE FROM tickets WHERE customer_id = 77;
ALTER TABLE tickets ADD CONSTRAINT fk_tickets_customer FOREIGN KEY (customer_id) REFERENCES customers (id);
SHOW CREATE TABLE tickets\G

Output

OUTPUT

+----+-------------+
| id | customer_id |
+----+-------------+
|  3 |          77 |
+----+-------------+
1 row in set

*************************** 1. row ***************************
       Table: tickets
Create Table: CREATE TABLE `tickets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tickets_customer` (`customer_id`),
  CONSTRAINT `fk_tickets_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

NULL in a foreign key

A foreign key column that allows NULL can be empty. A NULL means “no parent”, and is not checked. Use NOT NULL when the relationship is required.

Performance

Each insert or update of the child checks the parent through its index. Each delete or key change of the parent checks the child index.
Foreign keys add locks: updating a child row takes a shared lock on the parent row, which can matter in very hot tables.
Nobody should turn them off to be faster. The checks are cheap, and the cost of bad data is not.

Seeing the foreign keys

query.sql

SQL

SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'learn' AND referenced_table_name IS NOT NULL
ORDER BY table_name, constraint_name;

Output

OUTPUT

+---------------------+------------+-------------+-----------------------+------------------------+
| CONSTRAINT_NAME     | TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------+------------+-------------+-----------------------+------------------------+
| fk_orders_customer  | orders     | customer_id | customers             | id                     |
| fk_tickets_customer | tickets    | customer_id | customers             | id                     |
+---------------------+------------+-------------+-----------------------+------------------------+
2 rows in set

lightbulb

Foreign keys in big systems

Some very large systems skip foreign keys (for sharding or for bulk speed) and rely on application code. For almost everyone else, including most websites, keep them. They catch the bugs you did not know you had.