MySQL Database generate ERROR 1215 (HY000): Cannot add foreign key constraint

This is an article where the writing is made to explain and to describe a way or an alternative to handle the error generated from the execution of a query in MySQL Database. After executing a query in a SQL console which is solely to add a foreign key, the error which is shown in the title specifically stated ‘Cannot add foreign key constraint’.

The error is made in an attempt to add a foreign key which is intended for a third table in a many-to-many relationship. As we already knew, a way to handle a many-to-many relationship between two entities can be solved by creating another table which is the third one to save the identifier from each table.

Supposed if there is a table named ‘user’ and also another table named ‘role’ where a user can have many roles and a roles can be owned or possessed by many user. That condition is actually a many-to-many relationship between those two entities.

In a process of creating the third table, specifically in the process of creating a foreign key which is utilized as the connector to retrieve the information from the parent table, the error triggered. For an instance, in order to create a foreign key named ‘id_user’ in the new table for an example named ‘user_role’ which is going to be made, in certain circumstance, the error stated in the title showed up. Below is the description in the form of ERD (Entity Relationship Diagram) :

Below is the execution of the command in MySQL Command Console which is leading to the error generated from MySQL Database Server :

mysql> alter table user_role add constraint user_role_fk1 foreign key(id_user) references user(id_user) on delete cascade on update cascade;
ERROR 1215 (HY000): Cannot add foreign key constraint

The process of adding a foreign key failed as shown in the above content based on the error which is actually generated from MySQL Database Server : “Cannot add foreign key constraint”.

One of the solution which is executed to solve the above problem is executing the following command in MySQL Command Console :

set foreign_key_checks = 0;

Below is the execution of the above SQL command :

mysql> set  foreign_key_checks=0;
Query OK, 0 rows affected (0,01 sec)

After executing the above command, try to re-execute the previous command which is trying to add a foreign key :

mysql> alter table user_role add constraint user_role_fk1 foreign key(id_user) references user(id_user) on delete cascade on update cascade;
Query OK, 0 rows affected (0,07 sec)
Records: 0  Duplicates: 0  Warnings: 0

As shown above, adding foreign key has already succeed.