MySQL Bugs: #3058: DROP TABLE fails with ERROR 1217: Cannot delete or update a parent row: a

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ä	CPU Architecture:	Any

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;

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

I've experienced same bug in 4.1.2-alpha (custom build).
Workaround worked, but bug seems on limited to 4.0.18

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

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 ".

Sorry, forgot in previous mail: Windows 2000 system. Work around with "SET FOREIGN_KEY_CHECKS=0;" works.

Kind regards,

Jos

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