Conditional Unique Index on Multiple Columns

When we need to enforce uniqueness on a combination of table columns *only if* a certain condition is true, Oracle helps us implement this using a function-based index.

What is a Function-Based Index?

A function-based index is created on the value of a function or expression. The function used for building the index can be an arithmetic expression or an expression that contains a SQL function, user-defined PL/SQL function, package function, or C callout.

A typical use case for implementing a function-based index is case-insensitive search. Let’s say the column first_name in table employees contains mixed-case characters. If we have a standard index on first_name, and we use UPPER(first_name) in the WHERE clauses, the standard index will get ignored. We resolve this problem with this function-based index on the employees table:

CREATE INDEX emp_idx_f1
ON employees(UPPER(first_name));

Let’s see next how a function-based index can help with building conditional uniqueness on multiple columns in a table.

Function-based Index for Conditional Uniqueness

Take the case of a table that stores the audit trail of changes in additional to the latest active record:

SQL> -- Table that stores audit trail of
SQL> -- changes in addition to the active row
SQL> create table demo_fbi (
  2     col1 number
  3   , col2 number
  4   , col3 varchar2(30)
  5   , created_date date
  6   , active_flag char(1) default 'Y'
  7       check (active_flag in ('Y','N')));

Table created.

In the table above, we want (col1, col2) to work as a composite unique key conditionally i.e. only if active_flag = ‘Y’. There can be multiple occurrences of the same (col1, col2) combination for rows with active_flag = ‘N’.

We implement this using CASE WHEN ensconced in the CREATE UNIQUE INDEX statement:

SQL> -- Conditional unique index on multiple columns
SQL> create unique index demo_fbi_idx
  2  on demo_fbi
  3    (case when active_flag = 'Y' then
  4              col1 else null end,
  5     case when active_flag = 'Y' then
  6              col2 else null end);

Index created.

Here the searched CASE construct builds the index on (col1,col2) only if active_flag = ‘Y’, else it sets the value to (null,null) – note that in Oracle, all-NULL index entries are not recorded. This meets the desired result of having an index on (col1,col2) only if active_flag = ‘Y’.

Let’s test this!

We will test the behavior of the conditional unique index on multiple columns, with an insert statement script:

-- 1. Insert valid row
-- (1,1) for inactive row
insert into demo_fbi values
(1, 1, 'TEST1', sysdate,'N');

-- 2. Insert valid rows
-- (1,1) again for inactive row
insert into demo_fbi values
(1, 1, 'TEST12', sysdate,'N');

-- 3. Insert valid row
-- (1,1) for active row
insert into demo_fbi values
(1, 1, 'TEST3', sysdate,'Y');

-- 4. Insert invalid row
-- (1,1) again for active row
insert into demo_fbi values
(1, 1, 'TEST4', sysdate,'Y');

-- 5. Insert valid row
-- (1,2) for active row
insert into demo_fbi values
(1, 2, 'TEST5', sysdate,'Y');

-- 6. Insert invalid row
-- (1,2) again for active row
insert into demo_fbi values
(1, 2, 'TEST6', sysdate,'Y');

The script above attempts to insert 6 rows, of which 2 are expected to fail due to conditional unique index violation on (col1, col2). The details of rows expected to fail are given as comments in the script.

When run:

SQL> -- 1. Insert valid row
SQL> -- (1,1) for inactive row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST1', sysdate,'N');

1 row created.

SQL>
SQL> -- 2. Insert valid rows
SQL> -- (1,1) again for inactive row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST2', sysdate,'N');

1 row created.

SQL>
SQL> -- 3. Insert valid row
SQL> -- (1,1) for active row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST3', sysdate,'Y');

1 row created.

SQL>
SQL> -- 4. Insert invalid row
SQL> -- (1,1) again for active row
SQL> insert into demo_fbi values
  2  (1, 1, 'TEST4', sysdate,'Y');
insert into demo_fbi values
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEMO_FBI_IDX)
violated


SQL>
SQL> -- 5. Insert valid row
SQL> -- (1,2) for active row
SQL> insert into demo_fbi values
  2  (1, 2, 'TEST5', sysdate,'Y');

1 row created.

SQL>
SQL> -- 6. Insert invalid row
SQL> -- (1,2) again for active row
SQL> insert into demo_fbi values
  2  (1, 2, 'TEST6', sysdate,'Y');
insert into demo_fbi values
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEMO_FBI_IDX)
violated

The result is as expected — 4 rows were inserted, and two did not pass the conditional unique index validation.

SQL> select * from demo_fbi;

      COL1       COL2 COL3   CREATED_D ACTIVE_FLAG
---------- ---------- ------ --------- -----------
         1          1 TEST1  02-OCT-18 N
         1          1 TEST2  02-OCT-18 N
         1          1 TEST3  02-OCT-18 Y
         1          2 TEST5  02-OCT-18 Y

Summary

Oracle’s function-based indexes can be used to build conditional uniqueness on table columns. This article shows how a conditional unique index works on a table that stores, along with its active records, the audit trail of changes to its records.