Skip to content

How to Resolve Cannot add foreign key constraint in MySQL

  • by
3 Steps to Add a Foreign Key Constraint for MySQL

Cannot add foreign key constraint

Saw a MySQL ERROR 1215 (HY000): Cannot add foreign key constraint when I was trying to add a foreign key constraint on a table:

mysql> alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (id) on delete cascade on update cascade;
ERROR 1215 (HY000): Cannot add foreign key constraint

ERROR 1215 (HY000) Cannot add foreign key constraint

ERROR 1215 (HY000) Cannot add foreign key constraint

Rationale and Solutions for Cannot add foreign key constraint

Maybe the message of ERROR 1215 (HY000) is somewhat shallow. It only shows that it failed to create a foreign key constraint, but I don't see any hints or useful information in it. We have to find out the root cause by ourselves.

1. No Indexes on Both Columns

One obvious mistake is that you do not have indexes both on the referenced and referencing columns. So please make sure that you have indexes on the parent and child columns before adding any foreign key constraints.

The cure is pretty easy, just add indexes on columns that are required to have. For example, adding an index with an explicit name.

mysql> alter table customers add index customers_email_idx (email);

Or adding an index without naming it.

mysql> alter table customers add index (email);

If you still got "ERROR 1215 (HY000): Cannot add foreign key constraint", you can go on reading this post because things are getting complicated.

2. Column Definition Mismatch

Basically, we can't build referential relationship between two different data types of columns. Same data type of two columns is good to go. But how different is "different"? how same is "same"? Sometimes, they should be exactly the same even in every tiny specification.

I quote one precondition of adding a foreign key constraint from MySQL 8.0 Reference Manual below to sustain my point.

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

That's why we got ERROR 1215 (HY000) in the first place.

Now, let's go deeper to compare the difference between the referencing and referenced column.

mysql> desc orders;
...
| customer_id     | int(11)          |...
...

mysql> desc customers;
...
| id           | int(11) unsigned    |...
...

As you can see above, the column customer_id is not unsigned in the first table orders. So the final verdict is that they are different. That's why we cannot create the foreign key constraint. Is it too picky? Sorry, the integer column pair pursuits exactly the same.

So next, let's make them equivalent in size and sign by modifying the column customer_id as unsigned.

mysql> alter table orders modify column customer_id int(11) unsigned not null;

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Now, we can create the foreign key constraint without errors.

mysql> alter table orders add constraint orders_customer_id_fk foreign key (customer_id) references customers (id) on delete cascade on update cascade;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

Good! No more ERROR 1215 (HY000).

Referential Integrity (RI)

From now on, you have to respect the relationship. Everything you do such as update or delete is bound to the rigid relationship. The added foreign key constraint here will be used to enforce the referential integrity.

For example, you may not delete or update on the parent column because the child column prevents your change from violating the integrity. On the other hand, you may not add or update a child row because the child column must align its values with the parent's to keep the referential integrity that we defined early in this post.

Let's have a quick review on the terminology to clear the concept of foreign key constraint based on two columns. It helps you to understand more about ERROR 1215 (HY000).

RelationshipColumn 1Column 2
1ReferencingReferenced
2ChildParent
3TargetSource
4Foreign KeyReferenced Key
5FollowingFollowed

Essentially, a foreign key constrain is a column-based relationship, it involves between two columns, not two tables. That's why we can have self-referencing constraint on the same table.

Here is a reminder for you: The referenced key on the parent column and the referencing key on the child column must be created before adding a foreign constraint. Otherwise, you will get ERROR 1215 (HY000): Cannot add foreign key constraint.

Leave a Reply

Your email address will not be published.