MySQL REPLACE behaves differently than UPDATE if the PRIMARY KEY is referenced by a FOREIGN KEY

Question

I have two tables:

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(8) unsigned NOT NULL,
  `name` varchar(20) CHARACTER SET ascii NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `repeat_interval` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `color` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`category_id`),
  KEY `id` (`user_id`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON  DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `name` varchar(20) CHARACTER SET ascii NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `repeat_interval` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `color` mediumint(8) unsigned NOT NULL,
  `priority` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `start` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `done` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category`     (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If I make a REPLACE in the category table (one row) then all the entries in the event table referencing the modified row in the category table will be discarded.

But if I UPDATE a row in the category table then the entries in the event table are left untouched.

Why this behaviour, why when I REPLACE something all the entries referencing that column are discarded?

I tried with both ON UPDATE CASCADE and the default, same behaviour.

Google couldn't help me.

Answer 1

you have got ON DELETE CASCADE foreign key, and replace is simply "delete then insert new version" - it seems ON DELETE triggers are fired.

From Mysql Docs:

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. See Section 12.2.5, “INSERT Syntax”.

see http://dev.mysql.com/doc/refman/5.0/en/replace.html

To workaround this, you will probably want to use ON DUPLICATE KEY UPDATE insert syntax: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 2

the behavior of replace is as a delete and re-insert. this is useful when you are trying to update the primary key.

i presume with the foriegn key relation, you are getting a delete cascade followed by an insert into the table, but without inserts into the foreign table.