MySQL Error Code 1215: “Cannot add foreign key constraint”

MySQL Error Code 1215

Updated 7-05-2019

In 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:


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.

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.

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 parent to check that the local column and the referenced column both have the same data type and width.
How to fix: Edit your DDL statement such that the column definition in the child table matches that of the parent table.

5) The foreign object is not a KEY of any kind

How to diagnose: Use SHOW CREATE TABLE parent to check that if the REFERENCES 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.

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 parent to check if the REFERENCES 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.

7) Different charsets/collations among the two table/columns

How to diagnose: Run SHOW CREATE TABLE parent and compare that the child column (and table) CHARACTER 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.

8) The parent table is not using InnoDB

How to diagnose: Run SHOW CREATE TABLE parent and verify if ENGINE=INNODB or not.
How to fix: ALTER the parent table to change the engine to 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, 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.

10) The parent table is partitioned

How to diagnose: Run SHOW CREATE TABLE parent and find out if it’s partitioned or not.
How to fix: Removing the partitioning (i.e., merging all partitions back into a single table) is the only way to get it working.

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 parent and verify that the referenced column is not a virtual column.
How to fix: CREATE or ALTER the parent table so that the column will be stored and not generated.

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.

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.


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.

Share this post

Comments (15)

  • Valeriy Reply

    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 (
    -> parent_id INT(10) NOT NULL,
    ERROR 1215 (HY000): Cannot add foreign key constraint
    mysql> show engine innodb status\G
    *************************** 1. row ***************************
    Type: InnoDB
    2017-04-06 21:26:12 1b38 INNODB MONITOR OUTPUT
    Per second averages calculated from the last 10 seconds
    srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 129 srv_idle
    srv_master_thread log flush and writes: 131
    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
    2017-04-06 21:26:03 1b38 Error in foreign key constraint of table test/child:
    Syntax error close to:

    Way more informative than just error 1215 🙂

    April 6, 2017 at 2:28 pm
    • Marcos Albe Reply

      Nice! I gotta admit I never saw the errors there.

      April 6, 2017 at 2:41 pm
  • KarLKoX Reply

    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.

    May 12, 2017 at 5:12 am
  • luke Reply

    it works.Thank u very much

    June 14, 2017 at 7:55 am
  • Vicky Anand Reply

    Thanks Amazing details, for me it was character set issue

    September 19, 2017 at 6:33 pm
  • Michael Reply

    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.

    October 16, 2017 at 2:15 pm
  • Junaid Reply

    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.

    November 20, 2017 at 12:44 pm
  • Nabil Reply

    Saved my life bro..Thanks

    December 1, 2017 at 10:34 am
  • Barclay Dunn Reply

    AWESOME post! I’ve never run across different charsets being the issue before, so you saved my life too!

    September 25, 2018 at 4:43 pm
  • Fedrick Reply

    Very helpful, thanks a lot for the post!

    January 11, 2019 at 10:11 pm
  • Diego Carvalho Reply

    save me with 12°

    March 8, 2019 at 7:58 am
  • Aleksandr Mikheev (@ayamikh) Reply

    Thank you! You helped me a lot!

    March 19, 2019 at 9:29 am
  • Merianos Nikos Reply

    Awesome post !! You saved my day 🙂 Thank you very much 🙂

    March 23, 2019 at 1:40 pm
  • Archie Hughes Reply

    Thank you for sharing valuable information. Nice post. I enjoyed reading this post. soundcloud downloader

    April 3, 2019 at 11:32 pm
  • Aray Reply

    Thank you for this very helpful post.

    June 3, 2019 at 8:56 pm

Leave a Reply