如何在多列上创建组合键

Question

如何在多个列上创建复合主键，其中一个列可以具有某些值但不为空（或某个常量值）？

例如：

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

插入第四条记录应该会引发复合键违规错误。

- Mr.Burns

---

你提供的示例数据没有根据来拒绝主键为4的行，除非也拒绝主键为2的行。 - Jonathan Leffler

@Jonathan Leffler - 比我快了。我也有同样的问题。 - Thomas

@Mr.Burns - 第二行为什么没问题？如果没有主键，用户如何区分第一行和第二行中的数据？ - Thomas

你将无法创建符合你要求的基于键的约束。 - Jonathan Leffler

依我看，这是不可能的。 - hol

Answer 1

您希望实施一条规则，即在任何给定的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列作为第二个参数，因为它是主键，因此唯一。因此，该索引允许CANCELLED的重复值，除非它们为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>

- APC

Answer 2

这个能用基于唯一函数的索引来完成吗？类似于：

create unique index ix on tb (
    loc_id, date, time, decode(cancelled, null, 1, null));

- Alex Poole

---

当取消为“是”时，对于相同的loc_id、日期和时间组合，在Oracle中将无法工作。 - Michael Pakhantsov

APC的版本使用nvl2()更加简洁明了，并且已经测试通过，而这个版本还没有经过测试。但出于自己的考虑，我将null反转，实际上是有着相同的意图；但也许应该使用decode(cancelled, null, <some magic value>, pk)。其中魔法值可以是任何pk永远不会成为的值。这本身就很危险，所以…… - Alex Poole

Answer 3

如果规则是对于特定的LOC_ID、DATE_COL和TIME_COL组合只有一个NULL被取消值：

SQL> create table EXAMPLE
  2  (  PK        number       not null,
  3     LOC_ID    number       not null,
  4     DATE_COL  date         null,
  5     TIME_COL  varchar2(10) null,
  6     CANCELLED varchar2(3)  null,
  7     constraint EXAMPLE_PK primary key (PK)
  8  );

Table created.

SQL>
SQL> create unique index EXAMPLE_UK01 on EXAMPLE
  2    (case when CANCELLED is null then LOC_ID   else null end,
  3     case when CANCELLED is null then DATE_COL else null end,
  4     case when CANCELLED is null then TIME_COL else null end
  5  );

Index created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (1, 1, DATE '2010-01-01', '10:00AM', 'YES');

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (2, 1, DATE '2010-01-01', '10:00AM', 'YES');

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (3, 1, DATE '2010-01-01', '10:00AM', null);

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (4, 1, DATE '2010-01-01', '10:00AM', null);
INSERT INTO EXAMPLE VALUES
*
ERROR at line 1:
ORA-00001: unique constraint ([schema].EXAMPLE_UK01) violated

- Adam Musch

Answer 4

我不确定 Oracle 是否支持这个，但在 Postgresql 中，您可以通过对 null 创建一个部分多列索引来实现此操作，排除为 null 的列。

CREATE UNIQUE INDEX idx_foo
ON example ( Loc_ID, Date, Time )
WHERE canceled IS NULL

- Evan Carroll

---

不，row2的canceled不是NULL，这是一个关于NULL的部分索引。我相当确定你可以在Oracle中做到这一点，只是我不知道如何做，而且认为这可能是答案的一个很好的补充。 - Evan Carroll

好的，看到了。我相信SQL 2008也支持这个概念，但我认为Oracle还没有支持。 - Thomas

我不这么认为，我认为这可能是SQL 99。PostgreSQL至少已经支持了8年。 - Evan Carroll

哎呀，Oracle不支持这种语法。 - APC

没问题，这会给你一些需要搜索的东西：partial indexes 可以实现你想要的功能，去查一下在 Oracle 中如何实现它们。 - Evan Carroll