Debug Your Infrastructure
Get Instant Solutions for Kubernetes, Databases, Docker and more
MySQL 1215: Cannot add foreign key constraint.
When encountering error 1215: Cannot add foreign key constraint in MySQL, follow these steps:
- Check for Typo in Table or Column Names:
- Ensure the table and column names in the foreign key constraint are spelled correctly.
- Verify Table Engine:
- Both tables need to use the InnoDB storage engine. Check with:SHOW TABLE STATUS WHERE Name = 'yourtablename';
- Ensure Matching Column Types:
- The columns you are trying to reference must have the exact same data type and length. Check column types with:DESCRIBE yourtablename;
- Check for Existing Data Violations:
- Ensure that the existing data does not violate the foreign key constraint you are trying to create.
- Confirm Foreign Key Columns are Indexed:
- The referenced column(s) in the parent table must be indexed. Check indexes with:SHOW INDEX FROM yourtablename;
- Check for Duplicate Foreign Key Names:
- Ensure that the foreign key name is unique across the database.
- Review MySQL Error Log:
- Look into the MySQL error log for any additional messages related to this error.
- Try Creating the Foreign Key with a Simplified Syntax:
- Temporarily remove any `ON DELETE` or `ON UPDATE` clauses to isolate the issue.
- Check Foreign Key Constraints for the Referenced Table:
- Ensure there are no conflicting constraints on the referenced table that would prevent the addition of the new foreign key.
- Execute SHOW ENGINE INNODB STATUS:
- For a detailed explanation of the latest InnoDB foreign key error in the server, run:SHOW ENGINE INNODB STATUS;
Look under the `LATEST FOREIGN KEY ERROR` section.
Take action based on findings from these steps to resolve the specific cause of the error 1215 in MySQL.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
