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.

Cuprous

Your example data gives no basis for rejecting the row with PK = 4 when it doesn't also reject the one with PK = 2. – Illustrate

@Jonathan Leffler - Beat me to it. Had the same question. – Michalmichalak

Yes, PK = 2 is OK. Cancelled can have any value but not null repeating twice for the same locid,date and time. – Cuprous

@Cuprous - Why is the second row ok? Without the PK, how does a user differentiate the data in row 1 from row 2? – Michalmichalak

@Mr. Burns - is PK a database column? – Dominican

You are not going to be able to create a key-based constraint that meets the criteria you require. – Illustrate

In my opinion this is not possible. – Immolate

@ Mark Bannister - All rows will have some other columns which are used for audit trail purpose. Yes PK is the primary Key. – Cuprous

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>

Bonnice

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

Athalie

will not work in oracle for cases when cancelled is 'yes' for same combination of loc_id, date, time. – Sushi

APC's version with nvl2() is cleaner, and clearly works, whereas this is untested. Just for my own sake though, I'm reversing the null, effectively, which is about the same intention; but maybe should have done decode(cancelled, null, <some magic value>, pk). Where the magic value can be anything pk would never be. Which is clearly dangerous in itself, so... – Athalie

Answer 3

If the rule is that only one NULL cancelled value for a particular combination of LOC_ID, DATE_COL, and TIME_COL:

SQL> create table EXAMPLE
  2  (  PK        number       not null,
  3     LOC_ID    number       not null,
  4     DATE_COL  date         null,
  5     TIME_COL  varchar2(10) null,
  6     CANCELLED varchar2(3)  null,
  7     constraint EXAMPLE_PK primary key (PK)
  8  );

Table created.

SQL>
SQL> create unique index EXAMPLE_UK01 on EXAMPLE
  2    (case when CANCELLED is null then LOC_ID   else null end,
  3     case when CANCELLED is null then DATE_COL else null end,
  4     case when CANCELLED is null then TIME_COL else null end
  5  );

Index created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (1, 1, DATE '2010-01-01', '10:00AM', 'YES');

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (2, 1, DATE '2010-01-01', '10:00AM', 'YES');

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (3, 1, DATE '2010-01-01', '10:00AM', null);

1 row created.

SQL>
SQL> INSERT INTO EXAMPLE VALUES
  2    (4, 1, DATE '2010-01-01', '10:00AM', null);
INSERT INTO EXAMPLE VALUES
*
ERROR at line 1:
ORA-00001: unique constraint ([schema].EXAMPLE_UK01) violated

Catricecatrina

Answer 4

I'm not sure this is valid in Oracle, but in Postgresql you could do this with a partial multicolumn index on null, excluding the column that is null.

CREATE UNIQUE INDEX idx_foo
ON example ( Loc_ID, Date, Time )
WHERE canceled IS NULL

Anabiosis

No, row2's canceled IS NOT NULL, this is a partial index on NULL. I'm fairly certain you can do this in Oracle I just don't know how, and thought it might be good addition to the answers. – Anabiosis

Right. Saw that. I believe SQL 2008 also supports this notion but I do not think that Oracle does yet. – Michalmichalak

I don't think so, i think this is probably SQL 99. Postgresql has supported it for at least 8 years. – Anabiosis

Alas Oracle does not support this syntax. – Bonnice

That's fine it gives you things to search for: partial indexes do what you want, go look for how to do them in Oracle – Anabiosis