Description:
Hello,
I think there might be a bug in the cascading of deletes using foreign keys.
What I did was to create two tables, table1 and table2 of type InnoDB. After creating table2, I proceeded to add the foreign key constraints using ALTER TABLE, with ON DELETE before ON UPDATE.
Doing a show create table lists the ON UPDATE foreign key before the ON DELETE foreign key.
Next, I added in two records to table1 and table2, and tried to delete the record. The deletion fails with the error message:
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
However, if I were to issue ALTER TABLE with ON UPDATE before ON DELETE, things will be OK.
A complete transcript is in the next section.
How to repeat:
mysql> CREATE TABLE table1(
-> pk VARCHAR(2) not null,
-> data varchar(4) not null,
-> primary key(pk)
-> )type=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table table2(
-> fk varchar(2) not null,
-> data varchar(4) not null,
-> key(fk))type=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table table2 add foreign key table2(fk) references table1(pk) on delete cascade;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table table2 add foreign key table2(fk) references table1(pk) on update cascade;
Query OK, 0 rows affected (0.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table table2;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table2 | CREATE TABLE `table2` (
`fk` char(2) NOT NULL default '',
`data` varchar(4) NOT NULL default '',
KEY `fk` (`fk`),
CONSTRAINT `0_697` FOREIGN KEY (`fk`) REFERENCES `table1` (`pk`) ON UPDATE CASCADE,
CONSTRAINT `0_695` FOREIGN KEY (`fk`) REFERENCES `table1` (`pk`) ON DELETE CASCADE
) TYPE=InnoDB |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into table1 values('aa','aaaa');
Query OK, 1 row affected (0.02 sec)
mysql> insert into table2 values('aa','bbbb');
Query OK, 1 row affected (0.03 sec)
mysql> delete from table1 where pk='aa';
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql>
Suggested fix:
I'm sorry I am not able to provide a patch for this. I'm not capable of that. :(