MySQL Dropping Tables and Databases
05 Jul 2020
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.
#!/bin/bash
MYSQL_USER=root
MYSQL_PASSWORD=password
MYSQL=mysql
# 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)"`
>runlist.sql
>dropdbs.sql
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.
done
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
SET FOREIGN_KEY_CHECKS = 0;
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`;
...
...
SET FOREIGN_KEY_CHECKS = 1;
dropdbs.sql contents
drop database db1;
drop database db2;
drop database db3;
...
...