oracle db中具有多个字段的条件唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21088601/
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
Conditional unique constraint with multiple fields in oracle db
提问by James Freitas
I have this table:
我有这张表：
XPTO_TABLE (id, obj_x, date_x, type_x, status_x)
I wanna create a unique constraint that applies to the fields (obj_x, date_x, type_x)only when status_x <> 5.
我想创建一个唯一的约束，(obj_x, date_x, type_x)仅当status_x <> 5.
I have tried to create this one but Oracle says:
我试图创建这个，但 Oracle 说：
line 1: ORA-00907: missing right parenthesis
CREATE UNIQUE INDEX UN_OBJ_DT_TYPE_STATUS
ON XPTO_TABLE(
(CASE
WHEN STATUS_X <> 5
THEN
(OBJ_X,
TO_CHAR (DATE_X, 'dd/MM/yyyy'),
TYPE_X)
ELSE
NULL
END));
What's the correct syntax ?
什么是正确的语法？
回答by vishad
@jamesfrj: it looks like you are trying to ensure that your table should contain only one record for which status <>5.
@jamesfrj：看起来您正试图确保您的表应仅包含一条记录，其中status <>5.
You can try creating a unique functional index by concatenating the columns, as given below
您可以尝试通过连接列来创建唯一的功能索引，如下所示
create table XPTO_TABLE (id number,
obj_x varchar2(20),
date_x date,
type_x varchar2(20),
status_x varchar2(20)
);
create unique index xpto_table_idx1 on XPTO_TABLE(case when status_x <>'5' THEN obj_x||date_x||type_x||STATUS_x ELSE null END);
Hope it helps
希望能帮助到你
Vishad
维沙德
回答by Branko Dimitrijevic
Under Oracle 11, you can create a bunch of virtual columns that get non-NULL value only when STATUS_X is 5, and then make themunique:
在 Oracle 11 下，你可以创建一堆只有在 STATUS_X 为 5 时才获得非 NULL 值的虚拟列，然后使它们唯一：
CREATE TABLE XPTO_TABLE (
ID INT PRIMARY KEY,
OBJ_X INT,
DATE_X DATE,
TYPE_X VARCHAR2(50),
STATUS_X INT,
OBJ_U AS (CASE STATUS_X WHEN 5 THEN OBJ_X ELSE NULL END),
DATE_U AS (CASE STATUS_X WHEN 5 THEN DATE_X ELSE NULL END),
TYPE_U AS (CASE STATUS_X WHEN 5 THEN TYPE_X ELSE NULL END),
UNIQUE (OBJ_U, DATE_U, TYPE_U)
);
You can freely insert duplicates, as long as STATUS_X is not5:
您可以自由插入重复项，只要 STATUS_X不是5：
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (1, 1, '1-JAN-2014', 'foo', 4);
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (2, 1, '1-JAN-2014', 'foo', 4);
But trying to insert a duplicate when STATUS_X is 5 fails:
但是当 STATUS_X 为 5 时尝试插入重复项失败：
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (3, 1, '1-JAN-2014', 'foo', 5);
INSERT INTO XPTO_TABLE (ID, OBJ_X, DATE_X, TYPE_X, STATUS_X) VALUES (4, 1, '1-JAN-2014', 'foo', 5);
Error report -
SQL Error: ORA-00001: unique constraint (IFSAPP.SYS_C00139498) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
回答by Marco Montel
Because the CREATE UNIQUE INDEX is expecting only a value you can concatenate the columns as follow
因为 CREATE UNIQUE INDEX 只需要一个值，您可以按如下方式连接列
CREATE UNIQUE INDEX UN_OBJ_DT_TYPE_STATUS
ON XPTO_TABLE(
(CASE
WHEN STATUS_X <> 5
THEN OBJ_X || TO_CHAR (DATE_X, 'dd/MM/yyyy') || TYPE_X
ELSE
NULL
END));
