Question

我有两个表格:

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.

为什么这一行为,为什么在IREPLACE时,所有条目都提到该栏被丢弃?

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

谷歌可以帮助我。

Answer 1

页: 1

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”.

http://dev.mysql.com/doc/refman/5.0/en/replace.html“rel=“nofollow noreferer”>。 http://dev.mysql.com/doc/refman/5.0/en/replace.html。

为了解决这个问题,你可能希望使用<代码>。 http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html。

Answer 2

替代行为是删除和重新插入。当你努力更新主要钥匙时,这一点是有益的。

i 假定关键关系,你将删除一个栏目,然后在表格中插入一个字句,但没有在外表上插入。

Answer 3

You probably have ON DELETE CASCADE. REPLACE deletes the record from category table, which then deletes all event records assigned to that category.

Answer 4

我需要操作一次性升级文字,因此我的工作方向是:

Drop the foreign key constraints
Run my "REPLACE INTO" statements
Re-add the foreign key constraints

这将不作为守则中的一种永久解决办法,但它确实是一个快速的黑板。

友情链接