Updated 7-05-2019
In this blog, we’ll look at how to resolve MySQL error code 1215: “Cannot add foreign key constraint”.
This error often appears with little context:
|
1 |
ERROR 1215 (HY000): Cannot add foreign key constraint |
There are many possible causes. This guide covers the most common ones, how to diagnose them, and how to fix them.
Tip: Start by checking SHOW ENGINE INNODB STATUS and reviewing the LATEST FOREIGN KEY ERROR section.
Fix: Create the missing table or disable checks temporarily:
|
1 2 3 |
SET FOREIGN_KEY_CHECKS=0; SOURCE /backups/mydump.sql; SET FOREIGN_KEY_CHECKS=1; |
|
1 2 3 4 5 6 |
-- wrong REFERENCES `parent(id)` -- correct REFERENCES `parent`(`id`) REFERENCES parent(id) |
Fix: Verify with SHOW TABLES and SHOW COLUMNS.
|
1 2 3 4 5 |
-- wrong parent_id BIGINT -- correct parent_id INT |
|
1 |
ALTER TABLE parent ADD INDEX column_1_idx(column_1); |
|
1 |
ALTER TABLE parent ADD INDEX column_3_idx(column_3); |
Fix: Ensure both tables/columns use the same charset and collation.
|
1 |
ALTER TABLE parent ENGINE=INNODB; |
|
1 2 3 4 5 |
-- wrong REFERENCES parent -- correct REFERENCES parent(column_2) |
|
1 |
ALTER TABLE parent REMOVE PARTITIONING; |
|
1 2 3 |
ALTER TABLE parent DROP COLUMN column_virt, ADD COLUMN column_virt INT AS (...) STORED; |
Fix: Remove or replace SET DEFAULT.
|
1 2 |
-- fix parent_id INT NULL |
Many causes of error 1215 are due to MySQL limitations rather than mistakes. Understanding these constraints is key to resolving the issue.
More details: Foreign Key restrictions
Resources
RELATED POSTS
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 STATUSG, 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 statusG
*************************** 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.