DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL
  • Optimizing Data Management: Migrating From AWS RDS MySQL to Snowflake
  • Common Mistakes to Avoid When Writing SQL Code
  • SQL Commands: A Brief Guide

Trending

  • The Future of Java and AI: Coding in 2025
  • Beyond Code Coverage: A Risk-Driven Revolution in Software Testing With Machine Learning
  • Agentic AI for Automated Application Security and Vulnerability Management
  • FIPS 140-3: The Security Standard That Protects Our Federal Data
  1. DZone
  2. Data Engineering
  3. Databases
  4. Dealing With MySQL Error Code 1215: ''Cannot Add Foreign Key Constraint''

Dealing With MySQL Error Code 1215: ''Cannot Add Foreign Key Constraint''

Good old error 1215 is common enough, but MySQL doesn't give you the reason behind it. Fortunately, there are literally a dozen ways to dig deeper,

By 
Marcos Albe user avatar
Marcos Albe
·
Apr. 10, 17 · Tutorial
Likes (5)
Comment
Save
Tweet
Share
96.8K Views

Join the DZone community and get the full member experience.

Join For Free

MySQL error code 1215In 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

But MySQL never tells you exactly WHY it failed. There’s actually a multitude of reasons this can happen. This blog post is a compendium of the most common reasons why you can get ERROR 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 list:

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

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:

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:

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

# 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 parent to check that the local column and the referenced column both have 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.

Example:

# 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 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.

Example:

# 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 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.

Example:

# 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 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.

Example:

# 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 parent and verify if ENGINE=INNODB or not.

How to fix: ALTER the parent table to change the engine to InnoDB.

Example:

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

# 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 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.

Example:

# 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) 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.

Example:

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

# 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;


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!

If you know other ways MySQL can fail with ERROR 1215, let us know in the comments!

More information regarding Foreign Key restrictions can be found here: https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html.

Relational database Database MySQL Error code

Published at DZone with permission of Marcos Albe, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL
  • Optimizing Data Management: Migrating From AWS RDS MySQL to Snowflake
  • Common Mistakes to Avoid When Writing SQL Code
  • SQL Commands: A Brief Guide

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!