🚨 MySQL Error Guide: 'errno 150 Cannot add foreign key constraint' — Fix Mismatched Keys

Overview

MySQL raises error 1215 when a CREATE TABLE or ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY cannot be satisfied by the engine. The bare message is unhelpfully generic:

ERROR 1215 (HY000): Cannot add foreign key constraint

On MySQL 8.0 the SHOW ENGINE INNODB STATUS LATEST FOREIGN KEY ERROR section (and often the error itself) gives the real reason, frequently surfaced as OS errno 150:

ERROR 1005 (HY000): Can't create table 'app.#sql-1c2_3' (errno: 150 "Foreign key constraint is incorrectly formed")

Error 1215 tells you the foreign key was rejected; errno 150 is the underlying “incorrectly formed” code. The fix is always to make the child and parent columns truly compatible and ensure the referenced column is indexed.

Symptoms

• ALTER TABLE ... ADD FOREIGN KEY fails immediately with 1215, no rows touched.
• A migration tool (Rails, Django, Flyway, Liquibase) aborts on the FK step while every other statement succeeds.
• CREATE TABLE with an inline REFERENCES clause fails with errno 150.
• The same DDL works on one environment and fails on another (different engine defaults or charset).

Common Root Causes

• Type mismatch — child and parent columns differ in type, length, or signedness. INT vs BIGINT, or INT UNSIGNED vs INT are the classic offenders; both must match exactly.
• Charset / collation mismatch — for string keys (VARCHAR), the two columns must share the same character set and collation.
• Missing index on the parent column — the referenced column must be a PRIMARY KEY or have a UNIQUE/normal index. InnoDB will not reference an unindexed column.
• Storage-engine mismatch — foreign keys require both tables to be InnoDB. A parent or child still on MyISAM cannot participate.
• Orphaned data — when adding the FK to a populated table, existing child rows point to parent values that do not exist, so the constraint cannot be validated.
• Referencing a non-unique / partial parent key — the referenced columns must map to a complete unique/primary index, not a prefix or a subset of a composite key.
• Prefixed or generated column mismatch — using a column prefix length on one side, or referencing a virtual generated column.

Diagnostic Workflow

Get the real reason first. On InnoDB, the detailed cause lands in the engine status:

SHOW ENGINE INNODB STATUS\G
-- read the 'LATEST FOREIGN KEY ERROR' block

Compare the two column definitions side by side — type, length, signedness, charset, and collation must all line up:

SHOW CREATE TABLE parent_table\G
SHOW CREATE TABLE child_table\G

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND COLUMN_NAME IN ('id', 'parent_id')
  AND TABLE_NAME IN ('parent_table', 'child_table');

Confirm both tables are InnoDB:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME IN ('parent_table', 'child_table');

Verify the parent column is actually indexed as unique/primary:

SHOW INDEX FROM parent_table;

If the tables already hold data, find orphaned child rows that would fail validation:

SELECT c.parent_id
FROM child_table c
LEFT JOIN parent_table p ON p.id = c.parent_id
WHERE p.id IS NULL AND c.parent_id IS NOT NULL;

Example Root Cause Analysis

A team added an orders.customer_id foreign key referencing customers.id. The ALTER TABLE failed with 1215. SHOW ENGINE INNODB STATUS reported the FK was “incorrectly formed.”

SHOW CREATE TABLE revealed the mismatch: customers.id was BIGINT UNSIGNED NOT NULL (the ORM’s default primary key), while orders.customer_id had been hand-added as a plain INT. The signedness and width differed, so InnoDB rejected the constraint.

The fix was to align the child column, then re-add the key:

ALTER TABLE orders MODIFY customer_id BIGINT UNSIGNED NOT NULL;

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

Had the tables contained orphaned customer_id values, the second statement would still have failed — those rows would need to be cleaned up or repointed first. The FK then created cleanly.

Prevention Best Practices

• Define child FK columns by copying the parent’s exact type, length, and signedness — including UNSIGNED.
• Standardize on one storage engine (InnoDB) and one charset/collation (utf8mb4 / utf8mb4_0900_ai_ci on MySQL 8, utf8mb4_general_ci on MariaDB) across the schema so string keys always match.
• Ensure the referenced parent column is a PRIMARY KEY or has a UNIQUE index before writing any FK.
• Validate and clean data before adding a constraint to a populated table; run the orphan-row query above as a pre-flight check.
• Let migrations create the parent table and its index before the child references it; enforce ordering in your migration tool.

Quick Command Reference

SHOW ENGINE INNODB STATUS\G                 -- LATEST FOREIGN KEY ERROR detail
SHOW CREATE TABLE child_table\G             -- compare exact column defs
SHOW INDEX FROM parent_table;               -- confirm referenced column is indexed
-- find orphaned rows before adding the FK:
SELECT c.fk FROM child_table c LEFT JOIN parent_table p ON p.id = c.fk WHERE p.id IS NULL;
-- align type then add the constraint:
ALTER TABLE child_table MODIFY fk BIGINT UNSIGNED NOT NULL;
ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (fk) REFERENCES parent_table (id);

Conclusion

Error 1215 is almost never about the foreign key syntax and almost always about compatibility: the child and parent columns must be the same type, length, signedness, and (for strings) charset/collation, both tables must be InnoDB, and the parent column must be indexed. When adding to existing data, orphaned rows will also block the constraint. Read SHOW ENGINE INNODB STATUS for the real reason, align the definitions, clean the data, and the key creates without a fight.

MariaDB behaves the same on the fundamentals but tends to give a clearer error message inline (it will often name the mismatched column directly), whereas MySQL 8 pushes the detail into the InnoDB status block. Either way the remedy is identical.