oracle 如何在多列上创建组合键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3372707/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a composite key on multiple columns
提问by Mr.Burns
How can I create a composite key on multiple columns, one of which can have some value but not null (or some constant value)?
如何在多列上创建复合键，其中之一可以具有某些值但不能为空（或某些常量值）？
For example:
例如：
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
Insertion of the fourth record should raise a composite key violation error.
插入第四条记录应该会引发复合键违规错误。
采纳答案by APC
So what you what is to enforce a rule where only record cannot be cancelled for any given permutation of LOC_ID, DATE, TIME? We can do this with a function-based unique index.
那么，对于任何给定的 LOC_ID、DATE、TIME 排列，只能取消记录的规则是什么？我们可以使用基于函数的唯一索引来做到这一点。
This is what we want to avoid:
这是我们想要避免的：
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>
Let's build an index to enforce the rule
让我们建立一个索引来强制执行规则
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>
The NVL2()function is a special form of CASE which returns the second argument if the first argument is NOT NULL otherwise the third. The index uses the PK col as the second argument because it is the primary key and hence unique. So the index allows duplicate values of CANCELLED unless they are null:
该NVL2()函数是 CASE 的一种特殊形式，如果第一个参数为 NOT NULL，则返回第二个参数，否则返回第三个参数。索引使用 PK col 作为第二个参数，因为它是主键，因此是唯一的。所以索引允许重复的 CANCELED 值，除非它们为空：
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>
回答by Adam Musch
If the rule is that only one NULL cancelled value for a particular combination of LOC_ID, DATE_COL, and TIME_COL:
如果规则是只有一个 NULL 取消了 LOC_ID、DATE_COL 和 TIME_COL 的特定组合的值：
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
回答by Alex Poole
Could this be done with a unique function based index? Something like:
这可以通过基于唯一函数的索引来完成吗？就像是：
create unique index ix on tb (
loc_id, date, time, decode(cancelled, null, 1, null));
回答by Evan Carroll
I'm not sure this is valid in Oracle, but in Postgresql you could do this with a partial multicolumn index on null, excluding the column that is null.
我不确定这在 Oracle 中是否有效，但在 Postgresql 中，您可以使用 null 上的部分多列索引来执行此操作，不包括为 null 的列。
CREATE UNIQUE INDEX idx_foo
ON example ( Loc_ID, Date, Time )
WHERE canceled IS NULL
