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.