MySQL error 1215: Cannot add foreign key constraint
MySQL rejected a FOREIGN KEY definition: the column types differ, the parent isn't indexed, or the table engine doesn't support foreign keys.
- Error number
1215- Name
ER_CANNOT_ADD_FOREIGN- SQLSTATE
HY000- Category
- Keys and constraints
What it means
1215 says the foreign key couldn't be created but not why. MySQL 8.0 often reports a more specific error instead (3780 for incompatible columns, 1822 for a missing parent index, 1824 for a missing parent table). For the details, run SHOW ENGINE INNODB STATUS and read the LATEST FOREIGN KEY ERROR section.
Common causes
- The child and parent columns have different types, signedness (
INTversusINT UNSIGNED), length, or character set and collation. - The referenced columns aren't a primary key or don't start an index.
- One of the tables uses MyISAM or another engine without foreign key support.
- The parent table doesn't exist yet when the child is created.
ON DELETE SET NULLon a column declaredNOT NULL.
How to fix it
-
Read the detailed reason.
SHOW ENGINE INNODB STATUS; -- LATEST FOREIGN KEY ERROR
-
Compare the column definitions on both sides and make them identical.
SHOW CREATE TABLE customers; SHOW CREATE TABLE orders;
-
Make the column types match, then add the key.
ALTER TABLE orders MODIFY customer_id INT UNSIGNED NOT NULL; ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id);
-
Convert MyISAM tables to InnoDB.
ALTER TABLE customers ENGINE = InnoDB;
Related errors
3780Foreign key columns are incompatible1822Missing index for foreign key constraint1824Failed to open the referenced table1452Foreign key fails: cannot add child row
More keys and constraints errors
1062Duplicate entry for key1061Duplicate key name1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1048Column cannot be null3819Check constraint is violated1071Specified key was too long
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.