sql - 如何在多列上创建复合键

如何在多列上创建复合键，其中一列可以有一些值，但不能为null(或某些常数)？

例如:

PK    Loc_ID        Date                Time       Cancelled
1         1         01/01/2010        10:00AM        YES
2         1         01/01/2010        10:00AM        YES
3         1         01/01/2010        10:00AM        null
4         1         01/01/2010        10:00AM        null    - Not Acceptable

插入第四条记录会引发复合键冲突错误。

最佳答案

那么，您要执行什么规则才能在任何给定的LOC_ID，DATE和TIME排列中都不能取消记录的规则呢？我们可以使用基于函数的唯一索引来做到这一点。

这是我们要避免的事情:

SQL> select * from t34
  2  /

        PK     LOC_ID SOMEDATE   SOMETIM CAN
---------- ---------- ---------- ------- ---
         1          1 01/01/2010 10:00AM YES
         2          1 01/01/2010 10:00AM YES
         3          1 01/01/2010 10:00AM

SQL> insert into t34
  2  values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
  3  /

1 row created.

SQL>

让我们建立一个索引来执行规则

SQL> rollback
  2  /

Rollback complete.

SQL> create unique index t34_uidx
  2  on t34 (loc_id, somedate, some_time, nvl2(cancelled, pk, null) )
  3  /

Index created.

SQL>

NVL2()函数是CASE的一种特殊形式，如果第一个参数不为NULL，则返回第二个参数，否则返回第三个。索引使用PK col作为第二个参数，因为它是主键，因此是唯一的。因此，索引允许CANCELED的重复值，除非它们为null:

SQL> insert into t34
  2  values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
  3  /
insert into t34 values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T34_UIDX) violated


SQL>