Oracle – Table Check Constraint to Verify Unique Values

check-constraintsddloracle

Oracle 10g: Suppose the following table (T_REGISTER):

ID_PROCESS   PERIOD    CUSTOMER    STATUS
===========================================
0001234      201801    12300344    INVALID
0001236      201801    12300344    INVALID
0001246      201801    12300344    UNPAID
0001249      201801    12300344    UNPAID
0001278      201801    12300344    COMPLETED

ID_PROCESS is the PK.
But I also need that (PERIOD + CUSTOMER + STATUS) to be unique just when the STATUS is COMPLETED.

In other words, for one UNIQUE (PERIOD + CUSTOMER), I don't care having several INVALID or UNPAID rows, but I only can admit one unique (PERIOD;CUSTOMER; STATUS="COMPLETED")

Any elegant solution using CHECK CONSTRAINT before going for the trigger solution?
Thanks in advance…

Best Answer

Check constraints are not for enforcing uniqueness. Unique function-based index can do the job (because Oracle accepts multiple NULL values on unique columns). 

create table test1(ID_PROCESS   int , PERIOD int, CUSTOMER    int, STATUS varchar2(20));


create unique index idxu1_test1 on test1
    (case when STATUS ='COMPLETED' then PERIOD else null end,   
     case when STATUS ='COMPLETED' then CUSTOMER else null end 
     );


insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (1,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (2,1,1, 'invalid'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (3,1,1, 'invalid'); -- ok


insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (4,1,1, 'COMPLETED'); -- ok
insert into test1(ID_PROCESS   ,PERIOD, CUSTOMER    , STATUS )
values (5,1,1, 'COMPLETED'); -- ORA-00001: unique constraint violated
Related Question