Re: Selective uniqueness indexes

"dean" <deanbrown3d_at_yahoo.com> a écrit dans le message de news: 1181945032.687684.288640_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 Received on Sat Jun 16 2007 - 00:54:17 CDT