Tips & Tricks 4 : Conditional index

Client asked me if it is possible to create unique index on any table on condition. For example only 1 record is acceptable for any document which has active_flag as '1'. For active_flag '0' there can be more than 1 record.

In order to do this  we will create unique index with condition

SQL> create table t1 (document_id number,active_flag char(1));

Table created.

SQL> create unique index t1_idx on t1(case when active_flag='1' then document_id else null end);



For active_flag as '1' index will be populated with document_id , and for '0' not.
 
SQL> insert into t1 values (1,'1');

1 row created.

SQL> insert into t1 values (2,'1');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created.

SQL> insert into t1 values (1,'0');

1 row created. -- able to insert same record

SQL> insert into t1 values (1,'1');
insert into t1 values (1,'1')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_IDX) violated

-- Not able to insert same record for active_flag '1' value .