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 .
