Bug #3058 DROP TABLE fails with ERROR 1217: Cannot delete or update a parent row: a
Submitted: 3 Mar 2004 9:17 Modified: 2 Apr 2004 5:27
Reporter: Kevin Jones Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0.18-Standard OS:Linux (Linux Fedora Core-1)
Assigned to: Marko Mäkelä

[3 Mar 2004 9:17] Kevin Jones
Description:
I create a database (Blog) holding a number of tables one of which has a foreign key constraint to another. If I do a DROP DATABASE Blog; then the DROP fails with a 
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
. If I call DROP DATABASE Blog; again then it succeeds, also if I drop each table in turn then drop the DB it works.

How to repeat:
CREATE DATABASE Blog;
USE Blog;

CREATE TABLE blogentry
(
    BlogId BIGINT NOT NULL ,
    PRIMARY KEY (BlogId),
) TYPE=INNODB;

CREATE TABLE comments (
    BlogId BIGINT  NOT NULL ,
    CommentId INT NOT NULL,
    PRIMARY KEY (BlogId, CommentId),
	FOREIGN KEY (BlogId) REFERENCES blogentry (BlogId) ON DELETE CASCADE
) TYPE=INNODB;

DROP DATABASE Blog;
[3 Mar 2004 9:28] Heikki Tuuri
Kevin,

this will be fixed in 4.0.19 in the following way:

if you call DROP DATABASE and no table in the database is referenced by a FOREIGN KEY constraint from ANOTHER database, then DROP DATABASE will succeed.

Meanwhile, in 4.0.18 you can use

SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE ...
SET FOREIGN_KEY_CHECKS=1;

to drop a database.

Regards,

Heikki
[4 Mar 2004 14:35] Igor Mendelev
I've experienced same bug in 4.1.2-alpha (custom build).
Workaround worked, but bug seems on limited to 4.0.18
[2 Apr 2004 5:27] Marko Mäkelä
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.1764
[15 Aug 2004 21:57] Joske
I installed 4.0.20 with innodb tables. I have the same case with a "drop table if exists groups" command. This morning I still had myisam tables and this afternoon, I reloaded the (test) db with innodb tables.

When I now try to drop a table with the above construction, I get the error:"#1217 - Cannot delete or update a parent row: a foreign key constraint fails ".
[15 Aug 2004 21:58] Joske
Sorry, forgot in previous mail: Windows 2000 system. Work around with "SET FOREIGN_KEY_CHECKS=0;" works.

Kind regards,

Jos
[16 Aug 2004 9:40] Marko Mäkelä
The original bug report was about DROP DATABASE failing because of referential integrity constraints.
DROP TABLE should fail if the table contains rows that are referenced, and there are no ON DELETE CASCADE or ON DELETE SET NULL clauses in the foreign key declarations. Please refer to the documentation: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html