Bug #2418 REPLACE does not trigger ON DELETE CASCADE

Description:
REPLACE (and LOAD DATA with REPLACE option) does not trigger ON DELETE CASCADE.

How to repeat:
CREATE TABLE t1 ( a int NOT NULL, PRIMARY KEY (a) ) ENGINE=InnoDB;
CREATE TABLE t2 ( a int NOT NULL, PRIMARY KEY (a), FOREIGN KEY (a) REFERENCES t1(a) ON DELETE CASCADE ) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (1),(2),(3);
REPLACE INTO t1 VALUES (2);
SELECT * FROM t2;
DROP TABLE t1, t2;

Suggested fix:
It should be documented if this is by design.

This is not by design. I have updated the documentation about this issue to make it known until Heikki has fixed this.

Hi!

I have now fixed this in the 4.0 source tree. If a table is referenced by FOREIGN KEY constraints, then from now on sql_insert.cc is not allowed to resolve a duplicate key error in a way where it converts

DELETE + INSERT

to an UPDATE internally. Though, it may be that some users have thought about REPLACE as an UPDATE in this case, and may be surprised of this behavior change.

Regards,

Heikki