oracle db中具有多个字段的条件唯一约束

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.

我想创建一个唯一的约束,仅当status_x <> 5时才应用于字段(obj_x,date_x,type_x)。

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 ?

什么是正确的语法?

3 个解决方案

#1

@jamesfrj: it looks like you are trying to ensure that your table should contain only one record for which status <>5.

@jamesfrj:看起来您正在尝试确保您的表只包含一个状态<> 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

#2

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 them unique:

在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 not 5:

只要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.

#3

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));

#1