Tag Archives: Cannot delete or update a parent row: a foreign key constraint fails

[Solved] MYSQL Error: Cannot delete or update a parent row: a foreign key constraint fails

1 problem
when deleting data or tables associated with foreign keys, MySQL reports an error:

Cannot delete or update a parent row: a foreign key constraint fails

2 solution

SET foreign_key_checks = 0; // First set the foreign key constraint check off

drop table mytable; // Delete the data, table or view

SET foreign_key_checks = 1; // Turn on foreign key constraint checking to maintain the structural integrity of the table
First turn off foreign key constraints, perform the delete operation, and then turn on foreign key constraints

[Solved] MYSQL Error when deleting a table: Cannot delete or update a parent row: a foreign key constraint fails

Phenomenon

MySQL appears when deleting a table

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Why

It may be that in mysql, the foreign key association is set between the deleted table and another table, which makes it impossible to update or delete data

Solutions

By setting foreign_ KEY_ Checks variable to avoid this

Disable foreign key constraints

SET FOREIGN_KEY_CHECKS = 0;

Then you can delete the table

Start the foreign key constraint after deletion

SET FOREIGN_KEY_CHECKS = 1;

View current foreign_ KEY_ The value of checks can be determined by the following command

SELECT  @@FOREIGN_KEY_CHECKS;

Attention

This setting will only affect the current session, not the global

If you want to set global variables, you can write like this

SET GLOBAL FOREIGN_KEY_CHECKS = 0;