sql - 多列的唯一约束 - 允许单个空值

Question

我发现了这个:Unique constraint on multiple columns

SQL> CREATE TABLE t (id1 NUMBER, id2 NUMBER);

Table created
SQL> ALTER TABLE t ADD CONSTRAINT u_t UNIQUE (id1, id2);

Table altered
SQL> INSERT INTO t VALUES (1, NULL);

1 row inserted
SQL> INSERT INTO t VALUES (1, NULL);

INSERT INTO t VALUES (1, NULL)

ORA-00001: unique constraint (VNZ.U_T) violated

我想创建一个允许输入多个 (X,null) 值的约束，以便约束仅在约束所涉及的两个值都不为空时才生效。这可能吗？

Answer 1

请注意，您可以插入多个 (NULL,NULL)，但不能插入多个 (1,NULL)。这就是索引在 Oracle 中的工作方式；当所有列都为空时，则索引中没有条目。

因此，与其在 (id1,id2) 上构建普通索引，我们必须构建一个函数索引，当至少有一个值为 null 时，它使两个值都为 null。为此，我们需要确定性函数。首先 DECODE 检查是否为空。然后是 GREATEST，当至少一个值为 null 时，使用它会导致 null:

create unique index idx_t_unique on t 
( 
  decode(greatest(id1,id2),null,null,id1), 
  decode(greatest(id1,id2),null,null,id2) 
);

编辑(接受后 :-) 我刚刚看到，您不需要确定性函数，但也可以使用案例构造。也许情况总是如此，也许不是，我不知道。但是，如果您觉得它更具可读性，您也可以按如下方式编写索引:

create unique index idx_t_unique on t 
(
  case when id1 is null or id2 is null then null else id1 end,
  case when id1 is null or id2 is null then null else id2 end
);