Re: Unique index vs. unique constraint

A copy of this was sent to Michael Hjorth <michael_hjorth_at_my-deja.com> (if that email address didn't require changing) On Thu, 26 Aug 1999 09:00:56 GMT, you wrote:

>What is the functional difference between a unique index and a unique
>constraint?
>I know the latter has a constraint as well as the index, but apparently
>it enforces the same restrictions on the database as the unique index.
>Or did I get it wrong?
>Why would I prefer one over the other in a particular case?
>
>Thank you for any comments.
>
>Michael.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

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 Received on Thu Aug 26 1999 - 06:47:14 CDT