How to create a composite key on multiple columns

Question

How can I create a composite key on multiple columns, one of which can have some value but not null (or some constant value)?

For example:

PK    Loc_ID        Date                Time       Cancelled
1         1         01/01/2010        10:00AM        YES
2         1         01/01/2010        10:00AM        YES
3         1         01/01/2010        10:00AM        null
4         1         01/01/2010        10:00AM        null    - Not Acceptable

Insertion of the fourth record should raise a composite key violation error.

Answer 1

So what you what is to enforce a rule where only record cannot be cancelled for any given permutation of LOC_ID, DATE, TIME? We can do this with a function-based unique index.

This is what we want to avoid:

SQL> select * from t34
  2  /

        PK     LOC_ID SOMEDATE   SOMETIM CAN
---------- ---------- ---------- ------- ---
         1          1 01/01/2010 10:00AM YES
         2          1 01/01/2010 10:00AM YES
         3          1 01/01/2010 10:00AM

SQL> insert into t34 
  2  values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
  3  /

1 row created.

SQL>

Let's build an index to enforce the rule

SQL> rollback
  2  /

Rollback complete.

SQL> create unique index t34_uidx 
  2  on t34 (loc_id, somedate, some_time, nvl2(cancelled, pk, null) )
  3  /

Index created.

SQL>

The NVL2() function is a special form of CASE which returns the second argument if the first argument is NOT NULL otherwise the third. The index uses the PK col as the second argument because it is the primary key and hence unique. So the index allows duplicate values of CANCELLED unless they are null:

SQL> insert into t34 
  2  values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
  3  /
insert into t34 values (4 , 1 , to_date('01/01/2010','DD/MM/YYYY') , '10:00AM', null )
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T34_UIDX) violated


SQL>

Answer 2

Could this be done with a unique function based index? Something like:

create unique index ix on tb (
    loc_id, date, time, decode(cancelled, null, 1, null));