Updated 7-05-2019In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.
Our Support customers often come to us with things like “My database deployment fails with error 1215”, “Am trying to create a foreign key and can’t get it working” or “Why am I unable to create a constraint?” To be honest, the error message doesn’t help much. You just get the following line:
ERROR 1215 (HY000): Cannot add foreign key constraint
There’s actually a multitude of reasons this can happen, and in this blog post is a compendium of the most common reasons why you can get MySQL Error Code 1215, how to diagnose your case to find which one is affecting you, and potential solutions for adding the foreign key.
(Note: be careful when applying the proposed solutions, as many involve ALTERing the parent table and that can take a long time blocking the table, depending on your table size, MySQL version and the specific ALTER operation being applied; In many cases using pt-online-schema-change will be likely a good idea).
So, onto the solutions:
The best way to start investigating this error is by getting more information about it from LATEST FOREIGN KEY ERROR section of SHOW ENGINE INNODB STATUS. This will give you a hint regarding the problem, which should help you identify your case in the list below.
1) The table or index the constraint refers to does not exist yet (usual when loading dumps).
How to diagnose: Run SHOW TABLES or SHOW CREATE TABLE for each of the parent tables. If you get error 1146 for any of them, it means tables are being created in the wrong order.
How to fix: Run the missing CREATE TABLE and try again, or temporarily disable foreign-key-checks. This is especially needed during backup restores where circular references might exist. Simply run:
1 2 3 4 | 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; |
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | mysql> CREATE TABLE child ( -> id INT(10) NOT NULL PRIMARY KEY, -> parent_id INT(10), -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) -> ) ENGINE INNODB; ERROR 1215 (HY000): Cannot add foreign key constraint # We check for the parent table and is not there. mysql> SHOW TABLES LIKE 'par%'; Empty set (0.00 sec) # We go ahead and create the parent table (we’ll use the same parent table structure for all other example in this blogpost): mysql> CREATE TABLE parent ( -> id INT(10) NOT NULL PRIMARY KEY, -> column_1 INT(10) NOT NULL, -> column_2 INT(10) NOT NULL, -> column_3 INT(10) NOT NULL, -> column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, -> KEY column_2_column_3_idx (column_2, column_3), -> KEY column_4_idx (column_4) -> ) ENGINE INNODB; Query OK, 0 rows affected (0.00 sec) # And now we re-attempt to create the child table mysql> CREATE TABLE child ( -> id INT(10) NOT NULL PRIMARY KEY,drop table child; -> parent_id INT(10), -> FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) -> ) ENGINE INNODB; Query OK, 0 rows affected (0.01 sec) |
2) The table or index in the constraint references misuses quotes.
How to diagnose: Inspect each FOREIGN KEY declaration and make sure you either have no quotes around object qualifiers or that you have quotes around the table and a SEPARATE pair of quotes around the column name.
How to fix: Either don’t quote anything or quote the table and the column separately.
Example:
1 2 3 4 5 6 7 8 9 10 11 | # wrong; single pair of backticks wraps both table and column ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent(id)`; # correct; one pair for each part ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES `parent`(`id`); # also correct; no backticks anywhere ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id); # also correct; backticks on either object (in case it’s a keyword) ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(`id`); |
3) The local key, foreign table or column in the constraint references have a typo:
How to diagnose: Run SHOW TABLES and SHOW COLUMNS and compare strings with those in your REFERENCES declaration.
How to fix: Fix the typo once you find it.
Example:
1 2 3 4 5 | # wrong; Parent table name is ‘parent’ ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES pariente(id); # correct ALTER TABLE child ADD FOREIGN KEY (parent_id) REFERENCES parent(id); |
4) The column the constraint refers to is not of the same type or width as the foreign column:
How to diagnose: Use SHOW CREATE TABLE to check that the local column and the referenced column both have the same data type and width.parent
How to fix: Edit your DDL statement such that the column definition in the child table matches that of the parent table.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # wrong; id column in parent is INT(10) CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id BIGINT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ) ENGINE INNODB; # correct; id column matches definition of parent table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES `parent`(`id`) ) ENGINE INNODB; |
5) The foreign object is not a KEY of any kind
How to diagnose: Use SHOW CREATE TABLE to check that if the parentREFERENCES part points to a column, it is not indexed in any way.
How to fix: Make the column a KEY, UNIQUE KEY or PRIMARY KEY on the parent.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # wrong; column_1 is not indexed in our example table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB; # correct; we first add an index and then re-attempt creation of child table ALTER TABLE parent ADD INDEX column_1_idx(column_1); # and then re-attempt creation of child table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_1 INT(10), FOREIGN KEY (parent_column_1) REFERENCES `parent`(`column_1`) ) ENGINE INNODB; |
6) The foreign key is a multi-column PK or UK, where the referenced column is not the leftmost one
How to diagnose: Do a SHOW CREATE TABLE to check if the parentREFERENCES part points to a column that is present in some multi-column index(es) but is not the leftmost one in its definition.
How to fix: Add an index on the parent table where the referenced column is the leftmost (or only) column.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | # wrong; column_3 only appears as the second part of an index on parent table CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB; # correct; create a new index for the referenced column ALTER TABLE parent ADD INDEX column_3_idx (column_3); # then re-attempt creation of child CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_3 INT(10), FOREIGN KEY (parent_column_3) REFERENCES `parent`(`column_3`) ) ENGINE INNODB; |
7) Different charsets/collations among the two table/columns
How to diagnose: Run SHOW CREATE TABLE and compare that the child column (and table) parentCHARACTER SET and COLLATE parts match those of the parent table.
How to fix: Modify the child table DDL so that it matches the character set and collation of the parent table/column (or ALTER the parent table to match the child’s wanted definition.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # wrong; the parent table uses utf8/utf8_bin for charset/collation CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB; # correct; edited DDL so COLLATE matches parent definition CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_column_4 CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin, FOREIGN KEY (parent_column_4) REFERENCES `parent`(`column_4`) ) ENGINE INNODB; |
8) The parent table is not using InnoDB
How to diagnose: Run SHOW CREATE TABLE and verify if parentENGINE=INNODB or not.
How to fix: ALTER the parent table to change the engine to InnoDB.
Example:
1 2 3 4 5 6 7 | # wrong; the parent table in this example is MyISAM: CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY ) ENGINE MyISAM; # correct: we modify the parent’s engine ALTER TABLE parent ENGINE=INNODB; |
9) Using syntax shorthands to reference the foreign key
How to diagnose: Check if the REFERENCES part only mentions the table name. As explained by ex-colleague Bill Karwin in http://stackoverflow.com/questions/41045234/mysql-error-1215-cannot-add-foreign-key-constraint, MySQL doesn’t support this shortcut (even though this is valid SQL).
How to fix: Edit the child table DDL so that it specifies both the table and the column.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # wrong; only parent table name is specified in REFERENCES CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent ) ENGINE INNODB; # correct; both the table and column are in the REFERENCES definition CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, column_2 INT(10) NOT NULL, FOREIGN KEY (column_2) REFERENCES parent(column_2) ) ENGINE INNODB; |
10) The parent table is partitioned
How to diagnose: Run SHOW CREATE TABLE and find out if it’s partitioned or not.parent
How to fix: Removing the partitioning (i.e., merging all partitions back into a single table) is the only way to get it working.
Example:
1 2 3 4 5 6 7 8 9 | # wrong: the parent table we see below is using PARTITIONs CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY ) ENGINE INNODB PARTITION BY HASH(id) PARTITIONS 6; #correct: ALTER parent table to remove partitioning ALTER TABLE parent REMOVE PARTITIONING; |
11) The referenced column is a generated virtual column (this is only possible with 5.7 and newer)
How to diagnose: Run SHOW CREATE TABLE and verify that the referenced column is not a virtual column.parent
How to fix: CREATE or ALTER the parent table so that the column will be stored and not generated.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # wrong; this parent table has a generated virtual column CREATE TABLE parent ( id INT(10) NOT NULL PRIMARY KEY, column_1 INT(10) NOT NULL, column_2 INT(10) NOT NULL, column_virt INT(10) AS (column_1 + column_2) NOT NULL, KEY column_virt_idx (column_virt) ) ENGINE INNODB; # correct: make the column STORED so it can be used as a foreign key ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT(10) AS (column_1 + column_2) STORED NOT NULL; # And now the child table can be created pointing to column_virt CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_virt INT(10) NOT NULL, FOREIGN KEY (parent_virt) REFERENCES parent(column_virt) ) ENGINE INNODB; |
12) Using SET DEFAULT for a constraint action
How to diagnose: Check your child table DDL and see if any of your constraint actions (ON DELETE, ON UPDATE) try to use SET DEFAULT
How to fix: Remove or modify actions that use SET DEFAULT from the child table CREATE or ALTER statement.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # wrong; the constraint action uses SET DEFAULT CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT ) ENGINE INNODB; # correct; there's no alternative to SET DEFAULT, removing or picking other is the corrective measure CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE INNODB; |
I realize many of the solutions are not what you might desire, but these are limitations in MySQL that must be overcome on the application side for the time being. I do hope the list above gets shorter by the time 8.0 is released!
13) Using SET NULL for a constraint on a column defined as NOT NULL
How to diagnose: Check your child table DDL and see if the constraint column is defined with NOT NULL
How to fix: If the table already exists, then ALTER the table and MODIFY the column to remove the NOT NULL. Otherwise, edit your CREATE TABLE and remove the NOT NULL from the relevant column definition.
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | # wrong; the constraint column uses NOT NULL CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10) NOT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL ) ENGINE INNODB; # correct; make the parent_id column accept NULLs (i.e. remove the NOT NULL) CREATE TABLE child ( id INT(10) NOT NULL PRIMARY KEY, parent_id INT(10), FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET NULL ) ENGINE INNODB; |
If you know other ways MySQL Error Code 1215 occurs, let us know in the comments!
More information regarding Foreign Key restrictions can be found here.
Good post, but it seems one important detail is missing: you may find more details on why adding foreign key failed using SHOW ENGINE INNODB STATUS\G, like in this case:
mysql> create table parent(id int primary key);
Query OK, 0 rows affected (0.33 sec)
mysql> CREATE TABLE child (
-> id INT(10) NOT NULL PRIMARY KEY,
-> parent_id INT(10) NOT NULL,
-> FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT
-> ) ENGINE INNODB;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2017-04-06 21:26:12 1b38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
—————–
BACKGROUND THREAD
—————–
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 129 srv_idle
srv_master_thread log flush and writes: 131
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 7
OS WAIT ARRAY INFO: signal count 7
Mutex spin waits 3, rounds 90, OS waits 3
RW-shared spins 4, rounds 120, OS waits 4
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
————————
LATEST FOREIGN KEY ERROR
————————
2017-04-06 21:26:03 1b38 Error in foreign key constraint of table test/child:
FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE SET DEFAULT
) ENGINE INNODB:
Syntax error close to:
DEFAULT
) ENGINE INNODB
…
Way more informative than just error 1215 🙂
Nice! I gotta admit I never saw the errors there.
Hi,
I just found another way when mySQL fails to create a FK : the columns of the referenced table MUST MATCH the order of the PK !
I didn’t found anything in the web with this restriction.
it works.Thank u very much
Thanks Amazing details, for me it was character set issue
Found another reason, maybe a stupid one, but to me it happened:
If you want the constraint action to be “SET NULL”, then the child column needs to allow NULL-values in the table structure.
Thanks, thats a great info,
Valeriy – your InnoDB status query helped me to understand why I was getting an error.
Basically, I was trying to ‘SET NULL’ on delete and at the same time I was defining the column as NOT NULL.
Saved my life bro..Thanks
AWESOME post! I’ve never run across different charsets being the issue before, so you saved my life too!
Very helpful, thanks a lot for the post!
save me with 12°
Thank you! You helped me a lot!
Awesome post !! You saved my day 🙂 Thank you very much 🙂
Thank you for sharing valuable information. Nice post. I enjoyed reading this post. soundcloud downloader
Thank you for this very helpful post.
#5 got my error fixed – thank you for the detailed post.
So I have this assignment which mandates us to follow the given attribute and table names. Unfortunately, one of the table names is a reserved keyword- ‘Session’. Phpmyadmin doesn’t give any errors while creating the Session table but when I try to create another table that contains a foreign key referencing the Session table, I get the error “cannot add foreign key constraint”. Can you tell me how I should write the name of the session table so that phpmyadmin recognizes it as a table and not a keyword.
@Deepsha: you need to wrap the name with backticks;
So in general, whenver you want to use a reserved word it MUST be wrapped in backticks (`)
Great read. Tried all of the above and still unable to create FK Constraint on existing tables. I created two new tables and FK without any problem but Im trying to create FK with two existing tables – both empty. I made sure no typos. I have foreign field indexed. I have same type, width for foreign key and related primary key. Foreign field is not set to NOT NULL. Tried all. Still fails. Could I provide sql dumps of two tables (no records) and see if you spot something?
The tables I was able to add a FK for had type INT(11) for Primary and foreign keys. The tables I am unsuccessfully trying to add FK to have BIGINT(20) – could that be the problem?
I solved the problem. I found that the primary keys were created using attribute “UNSIGNED” while the foreign key fields did not have that attribute. I change the foreign key fields to “UNSIGNED” and bingo! Thanks again for your article though. It helped tremendously in diving into the details and there it was, as simple as that. Thank you.