Re: Unique index vs. unique constraint

A copy of this was sent to IT Division <gbkit_at_moc.kw> (if that email address didn't require changing) On Thu, 26 Aug 1999 12:42:30 +0300, you wrote:

>Hi Michael,
>
>There is no functional difference between these two because they are
>actually two sides of the same function. You cannot create a unique
>constraint
>without creating a unique index on the column (or columns). And vise versa,
>if
>you create an unique index, you will put a unique constraint as well.
>I would say that unique constraint is implemented through unique index.
>

it is not necessarily. Consider:

tkyte_at_8.0> create table t ( x int unique deferrable );

Table created.

tkyte_at_8.0>
tkyte_at_8.0> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
SYS_C007329                    NONUNIQUE

This is as of Oracle8.0 and up.

>VK.
>
>Michael Hjorth 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.

--
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:49:17 CDT