Re: unique index on upper

In Oracle 8.1.6+ (maybe 8.1.5?), you can try this:

create table atest (unq_text varchar2(20));

create unique index un_atest on atest (UPPER(unq_text));

SQL> insert into atest values('foo');

1 row created.

SQL> insert into atest values('Foo');
insert into atest values('Foo')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UN_ATEST) violated

If your version of Oracle is too old, you could write an update/insert trigger to check existing data and
raise an exception if a case-insensitive duplicate exists.

<seanldus_at_my-deja.com> wrote in message news:908pfe$il0$1_at_nnrp1.deja.com...
> Hi all,
>
> I would like to insure that a field on a table is unique (regardless of
> case) in other words if foo exists, I don't want to be able to insert
> Foo, etc.
>
> To do this would I put an unique index on upper of the field name?
>
> The oracle documentation says that if you want to create a unique
> constraint, that you should do just that(using alter table) and let the
> db create the index rather than using the create index command. Does
> this keep me from making the index on upper?
>
> Any help is appreciated.
>
> Thanks
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Dec 30 2000 - 00:33:01 CST