Bagi Chandrakasan
MySQL dropping tables and databases

MySQL Dropping Tables and Databases

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

The correct way is to solve the dependency issue and retry delete or update the parent table. Sometimes, there is a need to drop/delete them without fixing the underlying issue. Usually, when i try to drop the tables for reimport or recreation i ran into this issue.

Use FOREIGN_KEY_CHECKS=0 before drop/delete and run the statement. Enable it back with FOREIGN_KEY_CHECKS=1 afterwards.

mysql> set FOREIGN_KEY_CHECKS=0;
mysql> drop table mydb.mytable;
mysql> set FOREIGN_KEY_CHECKS=1;

Script to drop all databases and tables with the FOREIGN_KEY_CHECKS set.



# Modify the grep to exclude or include specific databases. The following excludes system schemas.
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)"`

echo "SET FOREIGN_KEY_CHECKS = 0;" > runlist.sql

for db in $databases; do

  echo "Backing up DB: $db"
  echo "use $db;" >> runlist.sql
  echo "drop database $db;" >> dropdbs.sql
  mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --add-drop-table --no-data $db |grep ^DROP >> runlist.sql
  # run mysqldump with no-data option to get DDL, grep for DROP statements and spool to file for each DB.

echo "SET FOREIGN_KEY_CHECKS = 1;" >> runlist.sql

The above script will produce two files runlist.sql which contains drop table statements and dropdbs.sql that contains drop database statements.

runlist.sql contents

use db1;
DROP TABLE IF EXISTS `db1_table1`;
DROP TABLE IF EXISTS `db1_table2`;
DROP TABLE IF EXISTS `db1_table3`;
use db2;
DROP TABLE IF EXISTS `db2_table1`;
DROP TABLE IF EXISTS `db2_table2`;
DROP TABLE IF EXISTS `db2_table3`;

dropdbs.sql contents

drop database db1;
drop database db2;
drop database db3;