From: Steve Hay
Date: February 26 2004 2:43pm
Subject: Bug in new FOREIGN KEY checks in MySQLInnoDB/4.0.18
List-Archive: http://lists.mysql.com/bugs/15255
Message-Id: <403E0601.7020609@uk.radan.com>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi,

I've just upgraded from MySQL 4.0.16 to 4.0.18 and now find that some 
commands that used to work without error (dropping and re-creating a 
database) no longer work.

The error is:

    error: 'Cannot delete or update a parent row: a foreign key 
constraint fails'

which presumably relates to the first item in the InnoDB version history 
(http://www.innodb.com/ibman.php#InnoDB.history).  (All the tables in 
the database concerned are InnoDB tables.)

It appears that the foreign key constraint is being checked even when 
dropping the database, which I assume is not intended.

I tried a simple case of one table X referencing another table Y, and 
"DROP DATABASE" works fine (even with data in it, so foreign key 
constraints are not being checked here, which is as I would expect).  I 
tried another case with two tables X and Y referencing one table Z, but 
still no error.  Finally, I reproduced my problem by having table X 
referencing table Y and table Y referencing table Z.

The following DOS command-lines result in the error above:

=====
C:\>C:\mysql\bin\mysqladmin -u root -f drop test
Database "test" dropped

C:\>C:\mysql\bin\mysqladmin -u root create test

C:\>C:\mysql\bin\mysql -u root test -e "create table foo(fooid int not 
null primary key) type=innodb; create table bar(barid int not null 
primary key, fooid int not null, index (fooid), foreign key (fooid) 
references foo (fooid)) type=innodb; create table baz(bazid int not null 
primary key, barid int not null, index (barid), foreign key (barid) 
references bar (barid)) type=innodb"

C:\>C:\mysql\bin\mysql -u root test -e "insert into foo values (1); 
insert into bar values (1, 1); insert into baz values (1, 1)"

C:\>C:\mysql\bin\mysqladmin -u root -f drop test
C:\mysql\bin\mysqladmin: DROP DATABASE test failed;
error: 'Cannot delete or update a parent row: a foreign key constraint 
fails'
=====

I'm running Windows XP SP1.

Regards,
- Steve



------------------------------------------------
Radan Computational Ltd.

The information contained in this message and any files transmitted with it are confidential and intended for the addressee(s) only.  If you have received this message in error or there are any problems, please notify the sender immediately.  The unauthorized use, disclosure, copying or alteration of this message is strictly forbidden.  Note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Radan Computational Ltd.  The recipient(s) of this message should check it and any attached files for viruses: Radan Computational will accept no liability for any damage caused by any virus transmitted by this email.