整个mysql表上的唯一键？

发布于 2024-10-22 18:33:12 字数 903 浏览 6 评论 0原文

假设我有一个包含两列的 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 已存在”错误消息，但是：

错误 1054 (42S22)：“where 子句”中未知列“不存在”

再次感谢！

原文

Suppose I have a mysql table with two columns: A and B. Is it possible to have a unique key so that I can only insert a value only once in either A or B (once in the whole table)?

So if column A contains 'qwe' and B contains 'asd' then these two values cannot be inserted anymore in either column.

this will not work:

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

thanks.

edit: I was able to accomplish this with the following trigger:

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;|

However, I do not see the 'A OR B already exists' error message, but:

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

Thanks again!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忘羡 2024-10-29 18:33:12

是的，这是可能的。

一种方法是

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

从这个博客文章

MySQL 触发器：如何中止 INSERT、UPDATE 或 DELETE
扳机？在 EfNet 的#mysql 上有人
问：
如果我的业务规则失败，如何让触发器中止操作？
在 MySQL 5.0 和 5.1 中，您需要
采取一些诡计来制造
触发失败并提供有意义的
错误信息。 MySQL 存储
程序常见问题解答说明了有关错误的信息
处理：
SP 11. SP 是否有“提出”声明来“提出应用程序错误”？抱歉，目前没有。 SQL 标准 SIGNAL 和 RESIGNAL 语句位于 TODO 上。
也许 MySQL 5.2 将包含 SIGNAL
声明这将使这个黑客
直接从 MySQL 存储中窃取
程序编程已过时。什么
是黑客吗？你要强行
MySQL 尝试使用一个列
不存在。丑陋的？是的。是吗
工作？当然。
创建触发器 mytabletriggerexample
插入前
对于每一行开始
IF(NEW.important_value) < （幻想 * 舞蹈 * 计算）然后
    声明虚拟 INT；

    选择您有意义的错误消息进入此处 INTO dummy 
        来自我的表
      WHERE mytable.id=new.id
结束如果；结尾;

另一种方式

您还可以使用事务

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

在触发器中写入错误条件：

set @error=1;

在过程中类似这：

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

Yes it's possible.

1 way is

You need to create a BEFORE INSERT TRIGGER and return error if the value is already found in other columns/tables.

From this 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;

Another way

You can also do with Transactions

use a procedure with transaction to insert data into transactional table (InnoDB),

In the trigger write on error condition:

set @error=1;

In the procedure something like this:

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

回复收藏 0 原文

风蛊 2024-10-29 18:33:12

以关系方式执行此操作的正确（且简单）方法是创建两个表 T1 和 T2，其中 T2 具有与 T1 的外键关系（多对一）。唯一索引/约束在 T2.yourUniqueColumn 上声明，如果您必须区分该列中的值，请向 T2 添加另一列：

   T1
   id
   foo


   T2
   t2id
   t1id  fk references T1
   yourUniqueColumn  [unique index/constraint]
   extraColumnToDescribeTheValueInUniqueColumn

T2 中自动递增 PK）：

  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select t1.id as t1id, T1.A as yourUniqueColumn, 'A' as extraColumn from T1


  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select T1.id as t1id, T1.B as yourUniqueColumn, 'B' as extraColumn from T1

您最初可以通过这种方式填充 T2（假设根据经验，每当您发现自己在使用关系数据库时按程序做事时，就应该退后一步并考虑重构。

The proper (and simple) way to do this relationally is to create two tables, T1 and T2, where T2 has a foreign key relationship (many-to-one) back to T1. The unique index/constraint is declared on T2.yourUniqueColumn, and if you must differentiate between the values in that column, add another column to T2:

   T1
   id
   foo


   T2
   t2id
   t1id  fk references T1
   yourUniqueColumn  [unique index/constraint]
   extraColumnToDescribeTheValueInUniqueColumn

You could initially populate T2 in this way (assumes auto-incrementing PK in T2):

  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select t1.id as t1id, T1.A as yourUniqueColumn, 'A' as extraColumn from T1


  insert into T2
  (t1id, yourUniqueColumn, extraColumn)
  select T1.id as t1id, T1.B as yourUniqueColumn, 'B' as extraColumn from T1

As a rule-of-thumb, whenever you find yourself doing things procedurally when working with a relational database, it's time to take a step back and consider refactoring.

回复收藏 0 原文