You can do this by running the following query in your MySQL client: SHOW ENGINE INNODB STATUS To help resolve this issue, you will need to identify the exact cause. 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. How to Find the Reason for the “Cannot Add Foreign Key Constraint” Error? The problem can be diagnosed by running SHOW CREATE TABLE parent and checking for ENGINE=INNODB.ĪLTER the parent table to switch from MySQL to InnoDB to resolve this issue. 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. 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. Different charsets/collations among the two tables/columns You can fix the problem by adding an index to the parent table where the referenced column is the leftmost or the only column. 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. The foreign key is a multi-column PK or UK, where the referenced column is not the leftmost one 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.įix the problem by making the column either a KEY, UNIQUE KEY, or a PRIMARY KEY. The foreign object is not a KEY of any kind 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 column the constraint refers to is not of the same type or width as the foreign columnįor 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 typo should be corrected as soon as it is discovered. 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 local key, foreign table, or column in the constraint references has a typo This can be resolved by either not quoting anything or quoting the table and the column separately. 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. SET or indexes in the constraint reference misuse quotes SOURCE /backups/mydump.sql - restore your backup within THIS session Run the following code to fix the error: SET FOREIGN_KEY_CHECKS=0 It is essential to perform this step during backup restorations in which circular references may be present. The solution is to run the missing CREATE TABLE and try again or temporarily disable foreign-key-checks. Any table that returns an error 1146 indicates that the tables have been created in the incorrect order. Run SHOW TABLES or SHOW CREATE TABLE on each of the parent tables. 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. Sometimes, the database table customers wish to add constraints does not exist, resulting in a 1215 error message. 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 In this way, you will gain a better understanding of the problem. 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. How to Fix the MySQL Cannot Add Foreign Key Constraint Error? What Is the Reason for MySQL “#1215 – Cannot Add Foreign Key Constraint” Error?
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |