如何在两个字段上创建“双面”唯一索引？

问题描述：

如何在表格中的两个字段上有效创建唯一索引，如下所示： create table t（integer，b integer）;如何在两个字段上创建“双面”唯一索引？

其中两个不同数字的任何唯一组合不能在表中的同一行上出现超过一次。

如果一行存在使得a = 1且b = 2，则另一行不能存在，其中a = 2且b = 1或a = 1且b = 2。换句话说，两个数字不能以任何顺序一次出现超过一次。

我不知道这样的约束被称为什么，因此标题中的'双面唯一索引'名称。

更新：如果我在列（a，b）上有一个组合键，并且数据库中存在一行（1,2），则可以在没有错误的情况下插入另一行（2,1）。我正在寻找的是被用来不止一次以任意顺序 ...

答

如何控制进入表格的内容，以便始终将最小数字存入第一列，并将最小数字存入第二列？只要“意味着”当然是相同的东西。在它进入数据库之前做它可能会更便宜。

如果这是不可能的，你可以保存领域的信息，但有他们在数字顺序复制成两个等领域上，您将创建主键（伪代码-ISH）：

COLUMN A : 2 
COLUMN B : 1 

COLUMN A_PK : 1 (if new.a < new.b then new.a else new.b) 
COLUMN B_PK : 2 (if new.b > new.a then new.b else new.a)

在应用程序中，这可以通过触发器（如Ronald的响应）轻松完成，或者处理得更高。

你的第一个答案对我来说是完美的。谢谢。我希望能有一个更“自然”的解决方案，但我想这就是它。 – 2008-10-23 17:20:21

答

见Two foreign keys instead of primary

这不涉及“以任何顺序”的要求 – 2008-10-23 12:23:42

答

我想这只能做到用一个方法，以防止同一对数从一个FOR INSERT触发器（结合两列上的唯一约束）。我的MySql语法不太流利（我的T-SQL更好），所以我想下面会包含一些错误：

编辑：清理了语法，因此它适用于MySQL。此外，请注意，您可能还想将其作为BEFORE UPDATE触发器（当然，使用不同的名称）。另外，这种方法依赖于在两个字段上有一个主键或其他唯一键（即，这个触发器只检查相反的东西是不存在的）。似乎没有任何方法可以抛出一个错误从一个触发器，所以我敢说这是一样好。

CREATE TRIGGER tr_CheckDuplicates_insert 
BEFORE INSERT ON t 
FOR EACH ROW 
BEGIN 
    DECLARE rowCount INT; 
    SELECT COUNT(*) INTO rowCount 
        FROM t 
        WHERE a = NEW.b AND b = NEW.a; 

    IF rowCount > 0 THEN 
     -- Oops, we need a temporary variable here. Oh well. 
     -- Switch the values so that the key will cause the insert to fail. 
     SET rowCount = NEW.a, NEW.a = NEW.b, NEW.b = rowCount; 
    END IF; 
END;

修正了并在MySQL下进行了测试。如果有人知道是否有可能直接从触发器中抛出一个错误来停止插入，那将会很方便知道...... – 2008-10-23 13:38:42

答

在Oracle中你可以使用一个基于函数的索引是这样的：

create unique index mytab_idx on mytab (least(a,b), greatest(a,b));

我不知道MySQL的，但也许类似的东西是可能的吗？例如，您可以在表中添加2个新列leastab和greatestab，并使用触发器分别保留值分别为最小值（a，b）和最大值（a，b），然后创建一个唯一索引，greatestab）。

如何在两个字段上创建“双面”唯一索引？

相关推荐