两列任意组合的唯一约束

I am trying to implement a unique constraint for a combination of two columns. I am running oracle 11g.

我正在尝试为两列的组合实现唯一约束。我正在运行oracle 11g。

Specifically I have two columns A and B.

具体来说,我有两列A和B.

I have a row like below

我有一排如下

A     B
1     2

Then I want the following combinations to fail when inserted

然后我希望以下组合在插入时失败

A     B
1     2
2     1

Is this possible to achieve with a unique index in Oracle?

这可能是通过Oracle中的唯一索引实现的吗?

2 个解决方案

#1

Yes, it is possible(for example using generated columns):

是的,它是可能的(例如使用生成的列):

CREATE TABLE tab(A INT NOT NULL, B INT NOT NULL);

ALTER TABLE tab ADD c1 AS (LEAST(A,B));
ALTER TABLE tab ADD c2 AS (GREATEST(A,B));
CREATE UNIQUE INDEX UQ_tab ON tab(c1,c2);

You could hide these columns if needed(Oracle 12c):

如果需要,您可以隐藏这些列(Oracle 12c):

ALTER TABLE tab MODIFY c1 INVISIBLE;
ALTER TABLE tab MODIFY c2 INVISIBLE;

DBFiddle Demo

EDIT:

Even simpler approach:

更简单的方法:

CREATE UNIQUE INDEX UQ_tab ON tab(least(A,B), greatest(A,B));

DBFiddle Demo

#2

You can use a UNIQUE INDEX with LEAST, GREATEST and COALESCE functions:

您可以使用具有LEAST,GREATEST和COALESCE功能的UNIQUE INDEX:

CREATE TABLE table_name (
  a INT,
  b INT
);

CREATE UNIQUE INDEX table_name__a__b__u ON TABLE_NAME(
  COALESCE( LEAST( a, b ), a, b ),
  GREATEST( a, b )
);

You need to include COALESCE in case one of the values is NULL.

如果其中一个值为NULL,则需要包含COALESCE。

DBFiddle

INSERT INTO table_name ( a, b ) VALUES ( 1, 2 );
1 rows affected
INSERT INTO table_name ( a, b ) VALUES ( 3, NULL );
1 rows affected
INSERT INTO table_name ( a, b ) VALUES ( 2, 1 );
ORA-00001: unique constraint (SCHEMA_NAME.TABLE_NAME__A__B__U) violated
INSERT INTO table_name ( a, b ) VALUES ( NULL, 3 );
ORA-00001: unique constraint (SCHEMA_NAME.TABLE_NAME__A__B__U) violated