MySQL Bugs: #6085: DROP TABLE fails if table is referenced

Bug #6085	DROP TABLE fails if table is referenced
Submitted:	14 Oct 2004 9:22	Modified:	30 Oct 2004 9:15
Reporter:	Georg Richter	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	all	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When specifying more then one table in a DROP statement, DROP fails if one table is referenced by 
another table. 
 

How to repeat:
5.0.2-alpha-debug-log 
[11:16] root@test> CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 
Query OK, 0 rows affected (0.10 sec) 
 
5.0.2-alpha-debug-log 
[11:17] root@test> CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), 
    -> FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE, 
    -> FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB; 
Query OK, 0 rows affected (0.13 sec) 
 
5.0.2-alpha-debug-log 
[11:17] root@test> drop table t1,t2; 
ERROR 1051 (42S02): Unknown table 't1' 
 
5.0.2-alpha-debug-log 
[11:17] root@test> drop table t1; 
Query OK, 0 rows affected (0.02 sec)

Verified with latest BK 4.1 and 5.0 trees.

With 4.0.22-debug-log I got:
mysql> drop table t1,t2;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

The fix for
BUG#5784 - Unable to drop table, error 1051
made the error message change. But besides the misleading error message, the bug report could be meant so that MySQL is expected to reorder the tables in a drop, so that foreign key constraints are respected. Or, that the command is repeated as long as a foreign key constraint was hit.
Georg, can you please specify, what you expect to happen?

Ingo, 
 
I don't think reordering might be a good solution, cause it will break BC. 
Same behaviour / error message as in 4.0.x would be ok. 
 
On the orther hand, it would be fine to have a solution to drop referenced tables on the fly, e.g: 
DROP t1, t2 CASCADE