mysql - 如何在不删除现有重复记录的情况下为 2 列添加唯一约束？

Question

我有这个表，col1 col2 col3，col1是主键，现在我想为col2和col3添加唯一约束，我使用了alter table添加约束，但是表中似乎已经有重复的记录，所以我必须删除它们才能使其工作，但是，如果我需要保留所有现有记录，我如何确保新添加的记录在 col2 和 col3 中具有唯一约束？

Answer 1

如果我理解正确的话，您想保留当前的重复项，但又想确保不添加任何新的重复项，对吧？就我个人而言，我会遵循上面的评论，但如果这不能满足您的业务需求，我理解。

不幸的是，约束不能满足您所描述的需求。您可能希望控制插入数据的软件中的重复逻辑，而不是在表级别。例如，如果表中尚不存在 val3，则以下内容只会添加行。合理？

INSERT INTO MyTable (col1, col2, col3, col4)
VALUES (val1, val2, val3, val4) WHERE NOT col3 = val3;

或者您可以触发 INSTEAD OF 触发器。 SQL 有一个特殊的临时“插入”表，您可以在输入值之前对其进行比较。所以您说，“插入这些值，但是等等!仅当临时“插入”表中的行与 MyTable 中的现有行不匹配时才插入它们。”

CREATE TRIGGER no_duplicates_in_MyTable ON MyTable
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF (NOT EXISTS (SELECT M.val3
      FROM MyTable M, inserted I
      WHERE M.val3 = I.val3))
   INSERT INTO MyTable
      SELECT val1, val2, val3, val4
      FROM inserted
END

或者您可以使用 AFTER INSERT 触发器在表级别处理逻辑，但这有点不稳定。在某些情况下，您可以删除现有的重复项。像这样的东西；

CREATE TRIGGER delete_duplicates_MyTable
AFTER INSERT ON MyTable
BEGIN
   DELETE FROM MyTable 
   WHERE val3 NOT IN (SELECT MIN(val3) 
                             FROM MyTable 
                             WHERE val1 = new. val1)
   AND val1 = new. val1;
END;

如果您有任何疑问或者这不能满足您的业务需求，请告诉我。

编辑:@Rik 是正确的，这些是 INSERT 触发器，因此它们不适用于更新，允许任何人的代码将值更改为重复值。尝试更新触发器。您可以尝试这些更改 INSERT 到 UPDATE 的操作。