Updated 7-05-2019
In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.
This error often appears with little context:
|
1 |
ERROR 1215 (HY000): Cannot add foreign key constraint |
There are many possible causes. This guide covers the most common ones, how to diagnose them, and how to fix them.
Tip: Start by checking SHOW ENGINE INNODB STATUS and reviewing the LATEST FOREIGN KEY ERROR section.
Fix: Create the missing table or disable checks temporarily:
|
1 2 3 |
SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; SET FOREIGN_KEY_CHECKS=1; |
|
1 2 3 4 5 6 |
-- wrong REFERENCES `parent(id)` -- correct REFERENCES `parent`(`id`) REFERENCES parent(id) |
Fix: Verify with SHOW TABLES and SHOW COLUMNS.
|
1 2 3 4 5 |
-- wrong parent_id BIGINT -- correct parent_id INT |
|
1 |
ALTER TABLE parent ADD INDEX column_1_idx(column_1); |
|
1 |
ALTER TABLE parent ADD INDEX column_3_idx(column_3); |
Fix: Ensure both tables/columns use the same charset and collation.
|
1 |
ALTER TABLE parent ENGINE=INNODB; |
|
1 2 3 4 5 |
-- wrong REFERENCES parent -- correct REFERENCES parent(column_2) |
|
1 |
ALTER TABLE parent REMOVE PARTITIONING; |
|
1 2 3 |
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT AS (...) STORED; |
Fix: Remove or replace SET DEFAULT.
|
1 2 |
-- fix parent_id INT NULL |
Many causes of error 1215 are due to MySQL limitations rather than mistakes. Understanding these constraints is key to resolving the issue.
More details: Foreign Key restrictions
Resources
RELATED POSTS
