Selective uniqueness indexes

Oracle 10g, 9.2i. Windows, Linux.

This is a statement that creates a unique index on field F1 in table T for cases where field IS_ACTIVE is 'Y':

create table T (IS_ACTIVE varchar2(1), F1 varchar2(10), F2 varchar2(10));

> Table created.

create unique index UNIQ_ACTIVE_RECS
on T ( case when IS_ACTIVE = 'Y' then F1 else NULL end);

> Index created.

Does anyone have an example of how to do this with 2 fields (say, F1, F2)? In my case its 2 varchar2 fields, so I can make up a concatenation such as this:

create unique index UNIQ_ACTIVE_RECS2
on T ( case when IS_ACTIVE = 'Y' then F1|| '____' || F2 else NULL end);

> Index created.

However, this is not an elegant solution and its open to errors in some (albeit unlikely) combinations of strings.

Thanks!

Dean Received on Fri Jun 15 2007 - 17:03:52 CDT