Bug #2418 REPLACE does not trigger ON DELETE CASCADE
Submitted: 15 Jan 2004 23:51 Modified: 9 Feb 2004 14:56
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.0.18 OS:
Assigned to: Heikki Tuuri Target Version:

[15 Jan 2004 23:51] Dean Ellis 
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.
[5 Feb 2004 0:58] Michael Widenius 
This is not by design. I have updated the documentation about this issue to make it known
until Heikki has fixed this.
[9 Feb 2004 14:56] Heikki Tuuri 
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