Mysql error 1452 – Cannot add or update a child row: a foreign key constraint fails

Today, when you add a foreign key to a mysql table, you always report an error. Here is the SQL statement:

Alter table A  

Add constraint FK_1 Foreign Key (‘ relation_ID ‘) References B(‘ id ‘) on UPDATE Cascade on DELETE Cascade

Error code: 1452
See the table is also no problem, and the field of RELATION_ID is no foreign key ah ~~~
Finally, a solution is found in Google. The general method is as follows:

mysql> SET foreign_key_checks = 0;

mysql> alter table tblUsedDestination add constraint f_operatorId foreign key(iOperatorId) references tblOperators (iOperatorId); Query
OK, 8 rows affected (0.23 sec) Records: 8  Duplicates: 0  Warnings: 0

mysql> SET foreign_key_checks = 1;

Make foreign_key_checks invalid first, then add foreign keys to the table, and finally make foreign_key_checks effective!
There is a reason for foreign_key_checks. If you cannot add a foreign key because it violates the constraint, you should correct the data first. Turning off checks and then adding keys puts you in an inconsistent state. Foreign key checks add overhead, and if you don’t want to use them, use myisam instead of

Read More: