Re: Creating Table as select...

From: Sameer Deshpande <sameer_deshpande_at_hotmail.com>
Date: Wed, 11 Feb 2004 17:30:47 +0100
Message-ID: <c0dld7$qec$02$1@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
> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message
news:<1076170588.168313_at_yasure>...
> > hrishy wrote:
> >
> > > Hi Daniel
> > >
> > > Can you please exaplin me why..that i should prefer unique constraints
> > > over unique indexes.
> > >
> > > regards
> > > Hrishy
> >
> > Can't defer unique index ... can defer unique constraint
> > Can't disable unique index ... can defer unique constraint
> > Only option with a unique index is to drop it
> >
> > Create a unique constraint and look at the entries in user_constraints
> > and user_indexes ... try the same thing after building a unique index.
Received on Wed Feb 11 2004 - 10:30:47 CST