How to fix MySQL "ERROR 1215: Cannot add foreign key constraint"

ERROR 1215 (HY000): Cannot add foreign key constraint

Common causes

• Column types differ, for example INT vs BIGINT, or SIGNED vs UNSIGNED
• Different character sets or collations on string key columns
• The parent column has no PRIMARY KEY or index
• One of the tables uses MyISAM, which has no foreign key support
• The parent table does not exist yet (migration order)
• Existing child rows reference parent values that do not exist (error 1452)

How to fix it

1. Get the detailed reason. Run SHOW ENGINE INNODB STATUS\G right after the error and read the LATEST FOREIGN KEY ERROR section. It says exactly which column or index is the problem.
2. Match the column types exactly. Run SHOW CREATE TABLE users; and SHOW CREATE TABLE orders;. The child column must have the same type, size and UNSIGNED flag, for example both BIGINT UNSIGNED.
3. Match charset and collation. For string keys, make both columns use the same charset and collation, such as utf8mb4 and utf8mb4_0900_ai_ci.
4. Index the parent column. The referenced column must be a PRIMARY KEY or have an index. Add one with ALTER TABLE users ADD UNIQUE INDEX (code); if needed.
5. Use InnoDB for both tables. Convert any MyISAM table with ALTER TABLE orders ENGINE=InnoDB;. Foreign keys are only enforced by InnoDB.
6. Fix orphan rows and migration order. Find child rows with no parent using a LEFT JOIN and fix or remove them. In migrations, create the parent table before the child.

Matching types for a working foreign key

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

How to stop it happening again

• Use one id type (such as BIGINT UNSIGNED) for all keys
• Set a default charset and collation for the whole database
• Create parent tables before child tables in migrations
• Use InnoDB everywhere