Bug #4292 Dropping multiple tables with foreign keys fails

• Submitted: 25 Jun 2004 23:31
• Modified: 28 Jun 2004 22:18
• Reporter: Michal Čihař
• Status: Won't fix
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (Debian/Linux)
• Assigned to: Heikki Tuuri
• CPU Architecture: Any

[25 Jun 2004 23:31] Michal Čihař

Description:
When dropping more tables with foreign keys just inside these, it simply fails because of constraints (see how to repat for sql dump):

 DROP TABLE `dep` ,
`dep_diag` ,
`diag` ;

MySQL said:
#1217 - Cannot delete or update a parent row: a foreign key constraint fails

When I drop all these tables, there is no problem with constraints....

How to repeat:
SQL dump of tables:

--
-- Table structure for table `dep`
--

CREATE TABLE dep (
  dep_id int(11) NOT NULL auto_increment,
  txt varchar(44) NOT NULL default '',
  PRIMARY KEY  (dep_id)
) TYPE=InnoDB;

--
-- Table structure for table `dep_diag`
--

CREATE TABLE dep_diag (
  dep_diag_id int(11) NOT NULL auto_increment,
  dep_id int(11) NOT NULL default '0',
  diag_id int(11) NOT NULL default '0',
  impact char(3) NOT NULL default '0',
  PRIMARY KEY  (dep_diag_id),
  KEY dep_id (dep_id),
  KEY diag_id (diag_id),
  CONSTRAINT `dep_diag_ibfk_1` FOREIGN KEY (`diag_id`) REFERENCES `diag` (`diag_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `dep_diag_ibfk_2` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`dep_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

--
-- Table structure for table `diag`
--

CREATE TABLE diag (
  diag_id int(11) NOT NULL auto_increment,
  txt varchar(243) NOT NULL default '',
  PRIMARY KEY  (diag_id)
) TYPE=InnoDB;

[28 Jun 2004 22:18] Dean Ellis

This is expected behavior due to the order in which you are dropping the tables.  You can circumvent that by using SET FOREIGN_KEY_CHECKS=0; before the DROP TABLE.

There is a note about this in the 4.0.18 changelog:

http://dev.mysql.com/doc/mysql/en/InnoDB_news-4.0.18.html