When encountering error 1215: Cannot add foreign key constraint in MySQL, follow these steps:
- Ensure the table and column names in the foreign key constraint are spelled correctly.
- Both tables need to use the InnoDB storage engine. Check with:SHOW TABLE STATUS WHERE Name = 'your
table
name';
- The columns you are trying to reference must have the exact same data type and length. Check column types with:DESCRIBE your
table
name;
- Ensure that the existing data does not violate the foreign key constraint you are trying to create.
- The referenced column(s) in the parent table must be indexed. Check indexes with:SHOW INDEX FROM your
table
name;
- Ensure that the foreign key name is unique across the database.
- Look into the MySQL error log for any additional messages related to this error.
- Temporarily remove any `ON DELETE` or `ON UPDATE` clauses to isolate the issue.
- Ensure there are no conflicting constraints on the referenced table that would prevent the addition of the new foreign key.
- 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.
(Perfect for DevOps & SREs)
(Perfect for DevOps & SREs)