Table check contraint to verify unique on specific value

2018-07-16 23:35:23

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

0001246 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...

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, CUSTOME

  • 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

    2018-07-17 01:16:52