Thursday, August 4, 2011

MYSQL - Disabling Foreign Key Checks in MYSQL

I faced a strange issue in using MySQL database. We had two tables and both were using referential integrity against each other. With such issue I was unable to drop those tables and I was facing the following error.

mysql> drop table persons_o;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails
mysql> show engine innodb status;
This command would give me the status of the last referential error happened.

I was able to fix the issue by dropping the Foreign Key Checks using the below command and then drop the tables successfully.

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

No comments:

Post a Comment