2列组合上的sql唯一约束

How can you create a unique constraint on a combination of two values in two columns.

如何在两列中的两个值的组合上创建惟一的约束。

meaning

意义

column1  column2 
   2        1

looking for the constraint to disallow

寻找不允许的约束

column1  column2 
   1        2

3 个解决方案

#1

If your database allows expressions in an index you can do something like this (ANSI SQL):

如果您的数据库允许在索引中使用表达式，您可以执行以下操作(ANSI SQL):

CREATE UNIQUE INDEX on your_table (least(column1, column2)
                              , greatest(column1, column2));

Note this is a unique index not a unique constraint. The only difference for most DBMS is that you can't have a unique index as the target of a foreign key, but otherwise they serve the same purpose.

注意，这是一个惟一的索引，而不是唯一的约束。对于大多数DBMS来说，惟一的区别是您不能使用唯一索引作为外键的目标，否则它们的作用是相同的。

If your DBMS does not have least() or greatest() you can replace that using a CASE expression:

如果您的DBMS没有最小()或最大()，您可以使用CASE表达式替换它:

create unique index on your_table 
  (case column1 < column2 then column1 else column2 end, 
   case column2 > column1 then column2 else column1 end));

#2

A unique constraint on 2 columns only prevents those exact 2 values being inserted (switching them is allowed):

两个列上的唯一约束只阻止插入这两个值(允许切换):

So you need A TRIGGER like this (ORACLE):

所以你需要一个像这样的触发器(ORACLE):

 CREATE TRIGGER trig1
        BEFORE INSERT ON TAB
        REFERENCING NEW AS NEW
        FOR EACH ROW
    DECLARE
        FOUND NUMBER;
    BEGIN
        SELECT COUNT(1) into FOUND FROM TAB WHERE
        (COLUMN1=:NEW.column2 AND COLUMN2=:NEW.column1) 
         OR (COLUMN1=:NEW.column1 AND COLUMN2=:NEW.column2); 
    IF FOUND>0 THEN
    raise_application_error (-20001,'INSERT not allowed');
    END IF;
        END trig1;

Warning: syntax not checked.

警告:语法不检查。

#3

Looking at the documentation, found this for the ORACLE SGBD :

查看文档，为ORACLE SGBD找到了这个:

CREATE TABLE b(
 b1 INT, 
 b2 INT, 
 CONSTRAINT bu1 UNIQUE (b1, b2) 
                USING INDEX (create unique index bi on b(b1, b2)),
 CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

Chapter "Specifying the Index Associated with a Constraint" on the page ORACLE documentation.

在页面ORACLE文档中“指定与约束相关的索引”一章。

Hop this help.

跳这种帮助。

#1