How do you fix the “MySQL Cannot Add Foreign Key Constraint” error? You may receive the following error message when attempting to add a foreign key constraint to a MySQL table: “ERROR 1215 (HY000): Cannot add foreign key constraint“.
An error like this is very general and does not provide specific details regarding the specific reason why MySQL cannot insert a foreign key constraint into a table. To resolve this issue, you will need to identify the exact cause of the problem.
This article will explain the reason for MySQL’s “#1215 – Cannot Add Foreign Key Constraint” error, how to find the reason, and how to fix it with four effective methods.
What Is the Reason for MySQL “#1215 – Cannot Add Foreign Key Constraint” Error?
This error can have a variety of causes. In order to determine the cause of this error, it is best to examine the LATEST FOREIGN KEY ERROR section of the SHOW ENGINE INNODB STATUS.
In this way, you will gain a better understanding of the problem. You will be able to identify your case from the following list:
Usually, when loading dumps, the table or index the constraint refers to does not exist
Sometimes, the database table customers wish to add constraints does not exist, resulting in a 1215 error message.
However, there is no indication that the table does not exist, so it is advised that you use the command to determine what tables are available in the database.
Run SHOW TABLES or SHOW CREATE TABLE on each of the parent tables. Any table that returns an error 1146 indicates that the tables have been created in the incorrect order.
The solution is to run the missing CREATE TABLE and try again or temporarily disable foreign-key-checks. It is essential to perform this step during backup restorations in which circular references may be present.
Run the following code to fix the error:
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SOURCE /backups/mydump.sql; -- restore your backup within THIS session
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
Tables or indexes in the constraint reference misuse quotes
If you wish to diagnose this error, inspect each FOREIGN KEY declaration and ensure that there are no quotes around object qualifiers or that there are quotes around the table and one pair around each column name.
This can be resolved by either not quoting anything or quoting the table and the column separately.
The local key, foreign table, or column in the constraint references has a typo
It is possible to diagnose this error by running SHOW TABLES and SHOW COLUMNS and comparing the strings to those specified in your REFERENCES declaration. The typo should be corrected as soon as it is discovered.
The column the constraint refers to is not of the same type or width as the foreign column
For diagnosis, use the SHOW CREATE TABLE parent to make sure that the local column and the referenced column contain the same type of data and width.
The problem can be resolved by editing your DDL statement to ensure that the column definition in the child table matches that in the parent table.
The foreign object is not a KEY of any kind
To diagnose this problem, use the SHOW CREATE TABLE parent to examine the REFERENCES part of the table to ensure that the column pointing to it is not indexed.
Fix the problem by making the column either a KEY, UNIQUE KEY, or a PRIMARY KEY.
The foreign key is a multi-column PK or UK, where the referenced column is not the leftmost one
In order to diagnose this error, perform the SHOW CREATE TABLE parent command to determine whether the REFERENCES part points to a column that is present in some multi-column indexes but is not the leftmost in its definition.
You can fix the problem by adding an index to the parent table where the referenced column is the leftmost or the only column.
Different charsets/collations among the two tables/columns
To diagnose the problem, run SHOW CREATE TABLE parent and verify that the child column’s CHARACTER SET and COLLATE parts match those on the parent table.
To fix this, make sure the child table DDL matches the character set and collation of its parent table/column, or ALTER the parent table so that its definition matches the child’s definition.
The parent table is not using InnoDB
The problem can be diagnosed by running SHOW CREATE TABLE parent and checking for ENGINE=INNODB.
ALTER the parent table to switch from MySQL to InnoDB to resolve this issue.
How to Find the Reason for the “Cannot Add Foreign Key Constraint” Error?
This error does not indicate the specific reason for MySQL’s inability to implement a foreign key constraint on the table, which is very general.
To help resolve this issue, you will need to identify the exact cause. You can do this by running the following query in your MySQL client:
SHOW ENGINE INNODB STATUS;Three fields are to be returned from this query: Type, Name, and Status. The LATEST FOREIGN KEY ERROR can be identified by expanding the status column of the result.
This section aims to identify why the last alter query failed so that you may fix the issue.
There is usually a mismatch between columns’ type in the primary table and the foreign table as the cause of this problem.
The problem can also be caused by a mismatch between two tables’ engine types, such as MyISAM and InnoDB.
It is also possible due to different collations between the tables, one using UTF-8 and the other using latin1.
You should pay attention to the following factors:
Engine: Each table should be configured with the same database engine, for example, InnoDB.
Datatype: In one table, both columns should have the same “datatype. int(11)“, whereas, in another, smallint(5) will cause a problem.
Collation: In order to ensure compatibility, both column charsets should be the same, e.g., UTF8.
Watchout: Columns still could have a different one, even if your tables have the same Collation.
Unique: Foreign keys should refer to unique fields in the reference table, usually primary ones.
Ungisned: It is possible for one table to have unsigned keys and another not to have any unsigned keys.
How to Fix MySQL “#1215 – Cannot Add Foreign Key Constraint” Error (4 Methods)?
Having discussed the different causes of this error message, we have moved on to the next step. This error needs to be fixed, so let’s see how to do it with four effective methods.
Method 1: Check for the table availability
To begin troubleshooting the error, you can check whether the table exists by running the following command:
show tables;It is evident from this code that the constraint could be created since the table was available.
Method 2: Make Sure the Parent Table is Using InnoDB
Further investigation could be conducted by checking the storage engine set to the database table. The following command can be used to do so:
SHOW CREATE TABLE parentThe result is that you will find that InnoDB is not the storage engine. As ENGINE=MYISAM was set, you can change the storage engine by running the following command:
ALTER TABLE parent ENGINE=INNODB;It is finally possible to fix the error with this method.
Method 3: Check if there are any inappropriate quotes
For every FOREIGN KEY declaration, ensure that no quotes are inserted around object qualifiers or that separate quotes surround the table and column names.
With this method, it is necessary either not to quote anything or to quote the column and the table separately to fix the error.
Method 4: Verifying Whether The Key Exists or Not
When entering MySQL commands, it is necessary to ensure that the correct command is entered. Cross-checking ensures that the customer is not referencing a nonexistent table key in the target table.
Therefore, it is necessary to correct the customers on how to use the key on the table. It is possible that the column in use for ON DELETE SET NULL does not have a null definition in some cases.
To prevent MySQL error 1215, you must ensure that the column is set to the default null value.
Conclusion
This article has explained the reason for MySQL’s “#1215 – Cannot Add Foreign Key Constraint” error, how to find the cause, and how to fix it with four effective methods.
Thank you for taking the time to review this article. It is our pleasure to present it to you. In the comment section, please post any questions or comments.
Please like and follow our Facebook and Twitter pages if you wish to remain up-to-date on the latest news.