Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 75 ยท Tables, Constraints and Schema Design

Foreign Keys in MySQL

Learn how MySQL foreign keys work in InnoDB: ON DELETE and ON UPDATE actions, FOREIGN_KEY_CHECKS, indexing rules and the cascade pitfalls to avoid.

A foreign key says that a value in one table must exist in another. Every orders.customer_id must match a row in customers, every comment belongs to a real post. With the rule in place the database refuses to create orphans: rows that point to something that no longer exists.

MySQL supports foreign keys, with conditions that surprise people coming from other databases. This lesson covers where they are enforced, the actions on delete and update, how to disable the checks safely, the indexing rules, and the cascade traps that cause real incidents.

What a foreign key protects

No orphans A child row needs a real parent.
Safe deletes A parent with children can't vanish by accident.
Documentation The schema shows how tables relate.
1 / 2

Declaring a foreign key

You can declare a foreign key inside CREATE TABLE or add one later with ALTER TABLE. In MySQL you should always use the table-constraint form with an explicit name:

SQL
CREATE TABLE customers (
  id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total_cents INT NOT NULL,
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB;

This block is read-only: AUTO_INCREMENT and UNSIGNED are MySQL syntax. The live examples below use SQLite's shorter syntax for the same idea.

The table that holds the foreign key column (orders) is the child. The table it points to (customers) is the parent.

MySQL also lets you write a short inline form, customer_id INT REFERENCES customers(id), directly in a column definition. The parser accepts it and then ignores it: no foreign key is created. Always use the FOREIGN KEY (...) REFERENCES ... form, and check with SHOW CREATE TABLE.

Where it is enforced

Only the InnoDB engine (and the clustered NDB engine) enforce foreign keys. If you create a table with ENGINE=MyISAM, MySQL parses the foreign key clause and discards it without a warning. Since InnoDB is the default and the engine you should use, this mostly bites on old schemas and imported dumps. The storage engines lesson shows how to check which engine a table uses.

Other limits to remember:

  • A partitioned InnoDB table cannot have foreign keys, and cannot be referenced by one.
  • A foreign key can only link tables inside the same server, not across servers.
  • Generated columns can take part only under restricted conditions, so avoid them.

The rules for a valid definition

Most "cannot add foreign key constraint" errors (MySQL error 1215, or 3780 for incompatible columns) come from breaking one of these rules.

  1. Matching types. The child and parent columns must have the same type, the same signedness and, for text, the same character set and collation. An INT child column does not match a BIGINT UNSIGNED parent. This is the number one cause of failure.
  2. An index on the parent column. It should be the primary key or a UNIQUE key. MySQL permits a non-unique index but that is a non-standard extension, so avoid it.
  3. An index on the child column. If you do not create one, MySQL creates it for you, named after the constraint. An index must exist because every parent delete has to find the matching child rows quickly, and every child insert has to find its parent.
  4. Existing rows must comply. When you add a constraint to a populated table, MySQL checks every row and fails if any child points to a missing parent.

ON DELETE and ON UPDATE actions

The actions say what happens to child rows when the parent row is deleted (ON DELETE) or its key value changes (ON UPDATE).

Action What happens to the child rows
RESTRICT The parent change is rejected.
NO ACTION In MySQL the same as RESTRICT, checked immediately.
CASCADE The child rows are deleted (or their key updated) automatically.
SET NULL The child column becomes NULL. The column must allow NULL.
SET DEFAULT Parsed, but rejected by InnoDB. Do not use it.

When you write no action, the default is RESTRICT behavior. In standard SQL NO ACTION can be deferred to the end of a transaction. MySQL cannot defer constraint checks at all: every row is checked at the moment of the statement, which sometimes forces you to insert parent rows before children in exactly the right order.

The error codes you will see are 1451 (Cannot delete or update a parent row: a foreign key constraint fails) when a parent change is blocked, and 1452 (Cannot add or update a child row) when a child points nowhere.

What the live runner does

The live runner is SQLite, and SQLite has an unusual default: foreign keys are off until you run PRAGMA foreign_keys = ON on the connection. That makes a useful experiment, because you can see what a schema does with and without enforcement:

SQL Foreign keys declared but not enforced

Press Run (or Ctrl + Enter) to see the result here.

The orphan Ghost has no author. The PRAGMA foreign_key_check result lists table books, row 2, parent authors. In MySQL with InnoDB the bad insert would have been rejected with error 1452, so this is the situation that foreign keys exist to prevent. Now turn enforcement on:

SQL With enforcement on, the orphan is rejected

Press Run (or Ctrl + Enter) to see the result here.

The run ends with FOREIGN KEY constraint failed. MySQL says ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails.

CASCADE and SET NULL in action

ON DELETE CASCADE fits child rows that have no meaning without the parent, such as order lines of an order. ON DELETE SET NULL fits optional links that should survive, such as a note that was written by a user who has since left. RESTRICT fits everything that must never be deleted by accident, such as invoices of a customer.

SQL CASCADE removes children, SET NULL keeps them

Press Run (or Ctrl + Enter) to see the result here.

Deleting Ada removes her two orders but keeps her note with customer_id set to NULL. Bo and everything attached to Bo is untouched.

Cascade pitfalls

Cascades are convenient and dangerous, and the danger grows with the data.

  • One statement, many rows. DELETE FROM customers WHERE id = 1 can silently remove millions of child rows across several tables in a single transaction, holding locks the whole time. Use RESTRICT for big tables and delete in batches.
  • Chains. A cascade can trigger a cascade. The following example deletes a whole management tree from one DELETE:
SQL A self-referencing cascade deletes the whole subtree

Press Run (or Ctrl + Enter) to see the result here.

Only Other survives: removing the boss deleted four people. InnoDB limits cascade depth to 15 levels, but that is far more than you want.

  • No triggers. In MySQL, cascaded actions do not fire triggers on the child table. An audit trigger on orders never sees the rows removed by ON DELETE CASCADE. Do your own auditing in application code, or avoid cascades on audited tables.
  • Locking. Inserting a child row takes a shared lock on the parent row, and updating the parent key locks the children. Busy parents become contention points. Locking is covered in Locking, Gap Locks and Deadlocks.
  • Write cost. Each child insert or update does an extra index lookup in the parent. This is cheap compared to a corrupt dataset, but it is not free, which matters on bulk loads.

FOREIGN_KEY_CHECKS

The session variable foreign_key_checks switches enforcement off for your connection. It exists for loading dumps, where tables are created and filled in an order that would otherwise break references. mysqldump writes SET FOREIGN_KEY_CHECKS=0; at the top of its output for that reason.

SQL
SET FOREIGN_KEY_CHECKS = 0;
-- load tables in any order, drop and recreate tables, truncate parents...
SET FOREIGN_KEY_CHECKS = 1;

This is read-only here because it is a MySQL setting. Turning checks back on does not verify the rows loaded in the meantime. Orphans created while the checks were off stay there. Verify afterwards with an anti-join:

SQL
SELECT o.id FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

SQLite's PRAGMA foreign_keys = OFF demonstrates the same risk live:

SQL Orphans created while checks are off survive

Press Run (or Ctrl + Enter) to see the result here.

The bad row stays after enforcement is back on, and only the explicit check finds it. Never leave foreign_key_checks off in application code. Set it only inside a deliberate maintenance script.

Should you use them?

Some teams skip foreign keys on very large or heavily sharded databases, because online schema changes and bulk deletes are harder with them. For most applications the right default is: use them. They catch bugs that tests miss, and they are the only guard against orphans created by a second program. If you drop them for scale, replace them with scheduled orphan checks, as above.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to a foreign key clause on a MyISAM table?

    Only InnoDB (and NDB) enforce foreign keys. Other engines accept the syntax and discard it without a warning.
  2. 2. A foreign key on an INT column referencing a BIGINT UNSIGNED primary key fails. Why?

    Both columns need matching types, signedness and, for strings, character set and collation. Mismatches are the most common cause of error 1215 or 3780.
  3. 3. Which option removes the child rows when the parent row is deleted?

    CASCADE deletes the dependent rows. SET NULL keeps them with a NULL link, and RESTRICT and NO ACTION reject the delete.
  4. 4. You loaded a dump with foreign_key_checks = 0 and then set it back to 1. What do you know about the loaded data?

    The setting only controls future checks. Verify the data yourself, for example with an anti-join query.

Summary

  • Foreign keys are enforced only by InnoDB. The inline REFERENCES form inside a column definition is ignored, so use FOREIGN KEY (...) REFERENCES ....
  • Child and parent columns need identical types, signedness and collation, and both sides need indexes (MySQL adds the child index if you forget).
  • RESTRICT blocks the parent change, CASCADE follows it, and SET NULL unlinks. NO ACTION equals RESTRICT, and SET DEFAULT is not supported.
  • Cascades can delete far more than expected, do not fire triggers on the child table, and add locking on busy parents.
  • foreign_key_checks = 0 is for loading dumps only. Re-enabling it does not re-validate data, so run an anti-join afterwards.
  • In the live runner, SQLite needs PRAGMA foreign_keys = ON first, which makes it a good place to practice the behavior.

Next: ALTER TABLE and Online DDL, where you change a table without taking your application down.