Description:
Hi!
When dropping a database that contains tables that reference each other, an error 1217 (failing foreign key constraint) occurs. All non-referenced tables are dropped, but not the referenced ones.
When dropping a database, I normally do not want to care about any database-internal foreign key violations that will of course occur.
Greetings,
Christian
How to repeat:
mysql> create database test3;
Query OK, 1 row affected (0.01 sec)
mysql> use test3;
Database changed
mysql> create table parent (id integer primary key) type=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table child (id integer primary key, parent_id integer, index par_ind(parent_id), foreign key(parent_id) references parent(id)) type=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into parent values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into child values (1,1);
Query OK, 1 row affected (0.01 sec)
mysql> drop database test3;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| parent |
+-----------------+
1 row in set (0.00 sec)
mysql> drop database test3;
Query OK, 0 rows affected (0.02 sec)
Suggested fix:
The following idea might help: In case of a "drop database", check if there are any references to tables outside of this database. If not, do not care about the references and just drop all the tables.
