检查 MySQL 中两列的唯一约束 [英] Unique constraint that check two columns in MySQL

问题描述

我想在我的 MySQL 表上添加一个唯一性约束.此表包含四列:

I would like to add a unicity constraint on my MySQL table. This table contains four columns :

ID | NAME | ADDRESS1 | ADDRESS2

此约束必须检查对于新行，新的 address1 和 address2 不包含在 ADDRESS1 和 ADDRESS2 中.

This constraint must check that for a new row, the new address1 and address2 are not contained either in ADDRESS1 nor ADDRESS2.

示例:

INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('A', 'B'); -- OK
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('C', 'D'); -- OK
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('E', 'A'); -- Fails because A exists in ADDRESS1
INSERT INTO MYTABLE (ADDRESS1, ADDRESS2) values ('D', 'F'); -- Fails because D exists in ADDRESS2

有没有办法定义这样的约束?

Is there a way to define a such constraint ?

推荐答案

你可以用 BEFORE 这种方式触发

You can do it with a BEFORE trigger this way

CREATE TRIGGER tg_bi_mytable
BEFORE INSERT ON mytable
FOR EACH ROW
  SET NEW.address1 = IF(EXISTS
     (
       SELECT * 
        FROM mytable 
       WHERE address1 IN(NEW.address1, NEW.address2) 
          OR address2 IN(NEW.address1, NEW.address2)
     ), NULL, NEW.address1);

注意:由于您使用的是缺少 SIGNAL 的 MySQL 版本，所以诀窍是违反其中一列的 NOT NULL 约束当找到具有相同地址的行时.

Note: Since you're using a MySQL version that lacks SIGNAL the trick is to violate NOT NULL constraint on one of the columns when rows with the same address have been found.

这是 SQLFiddle 演示.取消对最后一个插入语句之一的注释，然后单击 Build Schema.这些插入不会成功.

Here is SQLFiddle demo. Uncomment one of the last insert statements and click Build Schema. These inserts won't succeed.

这篇关于检查 MySQL 中两列的唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！