Re: Selective uniqueness indexes

On Jun 16, 1:54 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "dean" <deanbrow..._at_yahoo.com> a écrit dans le message de news: 1181945032.687684.288..._at_m36g2000hse.googlegroups.com...
> | 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
> |
>
> SQL> create unique index UNIQ_ACTIVE_RECS2
> 2 on T ( case when IS_ACTIVE = 'Y' then F1 end,
> 3 case when IS_ACTIVE = 'Y' then F2 end);
>
> Index created.
>
> Regards
> Michel Cadot

Thanks!

I presume that makes them unique as a whole pair, rather than unique independently? I will test.

Cheers,

Dean (ps where did you get this from?) Received on Sat Jun 16 2007 - 17:17:24 CDT