Bug #2418 REPLACE does not trigger ON DELETE CASCADE
Submitted: 15 Jan 2004 22:51 Modified: 9 Feb 2004 13:56
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.18 OS:
Assigned to: Heikki Tuuri CPU Architecture:Any

[15 Jan 2004 22: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.
[4 Feb 2004 23: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 13: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