mysql - 整个 mysql 表的唯一键？

Question

假设我有一个包含两列的 mysql 表:A 和 B。是否可以有一个唯一的键，以便我只能在 A 或 B 中插入一次值(在整个表中一次)？

因此，如果 A 列包含“qwe”而 B 列包含“asd”，则这两个值不能再插入任何一列。

这是行不通的:

UNIQUE KEY `A` (`A`,`B`),
UNIQUE KEY `A_2` (`A`),
UNIQUE KEY `B` (`B`),
UNIQUE KEY `B_2` (`B`,`A`)

谢谢。

编辑:我能够使用以下触发器完成此操作:

delimiter |
create trigger unique_check before insert on mytable
       for each row begin
              declare alreadyexists integer;
          select count(*) > 0 into alreadyexists from mytable
                 where A=NEW.B or B=NEW.A;
          IF alreadyexists = 1 THEN begin
             DECLARE dummy INT;
         SELECT 'A OR B already exists' INTO dummy FROM mytable
            WHERE nonexistent = 'value';
 end;
 END IF;
 END;|

但是，我没有看到“A OR B already exists”错误消息，但是:

ERROR 1054 (42S22): Unknown column 'nonexistent' in 'where clause'

再次感谢!

Answer 1

是的，这是可能的。

一种方式是

您需要创建一个BEFORE INSERT TRIGGER 并在该值已在其他列/表中找到时返回错误。

从这里blog post

MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger? On EfNet’s #mysql someone asked:

How do I make a trigger abort the operation if my business rule fails?

In MySQL 5.0 and 5.1 you need to resort to some trickery to make a trigger fail and deliver a meaningful error message. The MySQL Stored Procedure FAQ says this about error handling:

SP 11. Do SPs have a “raise” statement to “raise application errors”? Sorry, not at present. The SQL standard SIGNAL and RESIGNAL statements are on the TODO.

Perhaps MySQL 5.2 will include SIGNAL statement which will make this hack stolen straight from MySQL Stored Procedure Programming obsolete. What is the hack? You’re going to force MySQL to attempt to use a column that does not exist. Ugly? Yes. Does it work? Sure.

CREATE TRIGGER mytabletriggerexample
BEFORE INSERT
FOR EACH ROW BEGIN
IF(NEW.important_value) < (fancy * dancy * calculation) THEN
    DECLARE dummy INT;

    SELECT Your meaningful error message goes here INTO dummy 
        FROM mytable
      WHERE mytable.id=new.id
END IF; END;

另一种方式

您还可以使用 Transactions

使用带有事务的过程将数据插入事务表 (InnoDB)，

在错误条件下写入触发器:

set @error=1; 

在程序中是这样的:

set @error=0; 
start transaction 
do insert 
if @error>0 then rollback; 
else commit;