MySQL Bugs: #53163: REPLACE INTO fails even though foreign key has ON UPDATE CASCADE

Bug #53163	REPLACE INTO fails even though foreign key has ON UPDATE CASCADE
Submitted:	26 Apr 2010 14:05	Modified:	27 Apr 2010 9:01
Reporter:	Calle Kabo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	innodb, REPLACE, UPDATE

Description:
When replacing into a table that is referenced by another table it doesn't seem to be regarded as an update and the query fails.

How to repeat:
CREATE TABLE `a` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a_id` (`a_id`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `a` (`id`) VALUES ('1');
INSERT INTO `b` (`id`,`a_id`) VALUES ('1','1');
REPLACE INTO `a` SET `id` = 1;

"Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE);"

Sorry, but this is NOT a bug. Read the manual, http://dev.mysql.com/doc/refman/5.1/en/replace.html:

"REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE  index, the old row is deleted before the new row is inserted."

So, DELETE happens, and foreign key constraint prevents it:

77-52-4-109:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `a` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> 
mysql> CREATE TABLE `b` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `a_id` int(11) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `a_id` (`a_id`),
    ->   CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON
    -> UPDATE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO `a` (`id`) VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `b` (`id`,`a_id`) VALUES ('1','1');
Query OK, 1 row affected (0.00 sec)

mysql> update `a` set id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

ON UPDATE CASCADE works, as you can see from above, but:

mysql> REPLACE INTO `a` SET `id` = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE)

ON DELETE NO ACTION prevents this REPLACE from working. As soon as we enable cascade delete:

mysql> drop table b;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `b` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `a_id` int(11) unsigned NOT NULL,   PRIMARY KEY (`id`),   KEY `a_id` (`a_id`),   CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO `b` (`id`,`a_id`) VALUES ('1','1');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO `a` SET `id` = 1;
Query OK, 2 rows affected (0.00 sec)

everything works.

Hmm, I thought I read somewhere that if the row already exists the query is treated as an update. I must have been mistaken, evidently. My bad.
However, I still find your conclusion ("everything works") faulty. The behavior is not the expected. The row in table b is deleted, I wanted it to be updated (or even better, as the id isn't changed, the foreign key constraint shouldn't come into action at all).
Is there any way to do this w/o having to do a select for the row first to see if it exists, and then do a update or an insert based on that?

What about INSERT ... ON DUPLICATE KEY UPDATE? Check http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html.

Valeriy, yes, that works.
I added a data-field in table a to test:
insert into `a` (`id`, `data`) VALUES(1,'blabla')
ON DUPLICATE KEY UPDATE `data` = VALUES(`data`)

It would be nice if I could just write
insert into `a` (`id`, `data`) VALUES(1,'blabla')
ON DUPLICATE KEY UPDATE
and it would just figure out what to update automatically (everything that is not the unique key). But oh well, maybe in a future release :)