mysql - Upsert 用于组合不是主键的外键

Question

我有一个表格如下:

mytable (
  id int unsigned auto_increment primary key,
  user_id int unsigned not null,
  program_id int unsigned not null,
  description varchar(255),
  foreign key (user_id) references user(id),
  foreign key (program_id) references program(id)
)

我想做的事情:

• 我希望 user_id/program_id 组合是唯一的。
• 如果有人试图为相同的 user_id/program_id 在 mytable 中添加新描述，我希望更新当前行而不是作为新行插入。

我熟悉如何使用 INSERT...ON DUPLICATE KEY UPDATE 作为基本主键。当它们不是主键时，我需要做什么才能使其与重复的复合外键一起使用？

Answer 1

您可以创建复合 UNIQUE 约束，如下所示:

ALTER TABLE `mytable` ADD UNIQUE `my_composite_index`(`user_id`, `program_id`);

这将按预期使用 INSERT ... ON DUPLICATE KEY 语法工作，如 the documentation 中所述。 :

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.

INSERT INTO mytable (`user_id`, `program_id`, `description`) 
    VALUES(1, 2, 'my description')
    ON DUPLICATE KEY UPDATE `description` = 'my description'