我有这张桌子:
XPTO_TABLE (id, obj_x, date_x, type_x, status_x)
我想创建适用于领域的唯一约束(obj_x, date_x, type_x)
,只有当status_x <> 5
.
我试图创建这个,但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));
什么是正确的语法?
@jamesfrj:看起来你正在努力确保你的表只包含一个记录status <>5
.
您可以尝试通过连接列来创建唯一的功能索引,如下所示
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);
希望能帮助到你
Vishad
在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) );
只要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);
但是当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.