Re: Creating Table as select...

"Sameer Deshpande" <sameer_deshpande_at_hotmail.com> wrote in message news:c0dld7$qec$02$1_at_news.t-online.com...
> Hi,
>
> Though unique constraints have many advantages, I think in some
> requirements, we need to have unique index rather than unique constraint..
> Please correct me if I am wrong!
>
> consider the following senario!
>
> If I want to treat values 'Sameer' same as 'SAMEER', then I need to have
> unique index rather unique constraint. Unique constraint will allow me to
> have both of these values....
>
> SQL> create table tab1 (name varchar2(10));
>
> Table created.
>
> SQL> alter table tab1 add constraint c_unq_name unique (name);
>
> Table altered.
> SQL> insert into tab1 values ('Sameer');
>
> 1 row created.
> SQL> insert into tab1 values ('SAMEER');
>
> 1 row created.
> SQL> create table tab2 (name varchar2(10));
>
> Table created.
> SQL> create unique index ix_unq_name on tab2 (upper(name));
>
> Index created.
> SQL> insert into tab2 values ('Sameer');
>
> 1 row created.
> SQL> insert into tab2 values ('SAMEER');
> insert into tab2 values ('SAMEER')
> *
> ERROR at line 1:
> ORA-00001: unique constraint (SAMEER.IX_UNQ_NAME) violated
>
> SQL> select * from tab1;
>
> NAME
> ----------
> Sameer
> SAMEER
>
> SQL> select * from tab2;
>
> NAME
> ----------
> Sameer
>
>
> Please correct me!! Is it possible to same logic for unique constraints?
as
> I have created index as "upper(name)";
>
> Thanks
>
> Sameer

True, if you want case-insensitivity then a function-based index is the way to go, because you can't create a case-insensitive unique constraint. But I would regard that as a special case concerning case-insensitivity, not an argument for generally wanting to create unique indexes.

I happen to feel that the case of data arriving at the backed database should have been sorted out a long time ago by code running in the application so that precisely these sorts of workarounds and "tricks" can be avoided.

But that may just be me.

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------