Re: Unique index vs. unique constraint

This is all very interesting...but having read what Thomas wrote, I have a few more questions:

1. Doesn't oracle use index to internally implement unique constraint?
2. How come that unique index is not a business rule? Agreed that indexes are there just to boost performance, but if it is imposing uniqueness constraint on the incoming data, then it is indeed enforcing a business rule.
3. In the first example in the message pasted below, Oracle does not allow a NON UNIQUE index to be dropped because it is used by a unique constraint...This is further confusing..Why would a unique constraint be using a NON UNIQUE index.
4. Finally, is there a way to know which index is used by a particular constraint?
5. Can a table, that initially non deferrable, be made deferrable using alter table command?

I would really appreciate if this discussion continues to a point of sufficient fullfillment

Regards
Owais

> A unique index is very different from a unique constraint.
>
> A unqiue index is just an index. It will prevent a duplicate from
being entered
> into a table. It is generally not recognized by end user tools as
being a
> 'rule' in the database. It is not a rule in the database -- its just
an index.
>
> A unique constraint is a business rule. It tells you something about
the data.
> It may OR MAY NOT use a unique index as a tool to help it do its job.
Consider:
>
> tkyte_at_8.0> create table t ( x int );
> Table created.
>
> tkyte_at_8.0> create index t_idx on t(x);
> Index created.
>
> tkyte_at_8.0> alter table t add constraint x_is_unique unique(x);
> Table altered.
>
> tkyte_at_8.0> select table_name, uniqueness
> 2 from user_indexes
> 3 where table_name = 'T'
> 4 /
>
> TABLE_NAME UNIQUENES
> ------------------------------ ---------
> T NONUNIQUE
>
> tkyte_at_8.0> drop index t_idx;
> drop index t_idx
> *
> ERROR at line 1:
> ORA-02429: cannot drop index used for enforcement of unique/primary
key
>
> So, as you can see -- a unique constraint may or may not use a primary
key.
> Additionally, in Oracle8.0 and up, a unique constraint may be
deferrable -- not
> immediately enforced whereas a unique index is always immediately
enforced.
> Consider:
>
> tkyte_at_8.0> create table t ( x int unique deferrable );
> Table created.
>
> tkyte_at_8.0> select index_name, uniqueness from user_indexes where
table_name =
> 'T';
>
> INDEX_NAME UNIQUENES
> ------------------------------ ---------
> SYS_C007329 NONUNIQUE
>
> tkyte_at_8.0> set constraints all deferred;
> Constraint set.
>
> tkyte_at_8.0> insert into t values ( 1 );
> 1 row created.
>
> tkyte_at_8.0> insert into t values ( 1 );
> 1 row created.
>
> tkyte_at_8.0> insert into t values ( 1 );
> 1 row created.
>
> tkyte_at_8.0> insert into t values ( 1 );
> 1 row created.
>
> tkyte_at_8.0>
> tkyte_at_8.0> commit;
> commit
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-00001: unique constraint (TKYTE.SYS_C007329) violated
>
> So, here we have a unique constraint that CREATED a non-unique index
and allows
> us to do something that we cannot do with a unique index. During the
course of
> our transaction, we can make the table have dups and upon commit the
system will
> ensure the data is consistent.
>
> I would almost always pick the unique constraint over a unique index
as
>
> - it has more options
> - it means more, its a rule -- an index is just an index.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 31 1999 - 05:51:27 CDT