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

如何控制进入表中的内容,以便始终将最小的数字存储到第一列,而将最大的数字存储到第二列?当然,只要“意思”是相同的。在它到达数据库之前做这件事可能要便宜一些。

如果这是不可能的,您可以按原样保存这些字段,但将它们按数字顺序复制到另外两个字段中,然后在其中创建主键(伪代码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 )

这可以很容易地通过一个触发器(如罗纳德的响应)来完成,或者在应用程序中处理得更高。

我认为这只能使用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;

在Oracle中,可以使用基于函数的索引,如下所示:

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

我不知道MySQL,但可能有类似的东西吗?例如,您可以向表中添加两个新列least a b和greatest a b,使用触发器分别使用最小值(a,b)和最大值(a,b)维护它们,然后在(least a b,greatest a b)上创建唯一索引。

见 Two foreign keys instead of primary