| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Unique index vs. unique constraint
Sorry I don't have time right now to write a response, but go to our website
http://www.rbsbooks.com/lopchap6.htm which is a sample chapter (on
referential integrity) from my tutorial, Learning Oracle Database
Programming. You'll find a full answer to your question there.
Frances Edelstein
Relational Business Systems
fran_at_rbsbooks.com
>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 - 11:10:12 CDT
 |
 |