Conditional unique constraint with multiple fields in oracle db

conditional-statements, oracle, sql, unique, unique-constraint

Solution

@jamesfrj: it looks like you are trying to ensure that your table should contain only one record for which `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);

Problem

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`. I have tried to create this one but Oracle says: ``` 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 ?

Original source