Bug #2535 Foreign Keys on InnoDB tables not honoured.

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. :(

You only need a single foreign key for this:

ALTER TABLE table2 ADD FOREIGN KEY table2(fk) REFERENCES table1(pk) ON UPDATE CASCADE ON DELETE CASCADE;

However, I am verifying this because (with 4.0.18) when multiple foreign keys have been declared like this, neither cascade succeeds (both the update and the delete fail to cascade), so some action on this seems warranted (whether documentation, throwing an error during FK creation or cascading the keys).

Thank you.

Hi!

In your script you are creating TWO different foreign key constraints on the tables. You should only create one constraint:

alter table table2 add foreign key table2(fk) references table1(pk)
on delete cascade on update cascade;

In the future, InnoDB will get deferred constraints, which will allow the deletion even if that temporarily breaks one of the constraints.

Best regards,

Heikki