Re: Difference: Uniq. Constraint & Index ?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 17 Oct 2006 10:46:31 -0700
Message-ID: <1161107186.878433@bubbleator.drizzle.com>

Robert Klemme wrote:
> On 17.10.2006 03:20, DA Morgan wrote:

>> However I would argue that you should never build a unique index.
>>
>> Here are just a few of the differences:
>> 1. Unique constraints put an entry into user_indexes
>> 2. Unique indexes do not create an entry in user_constraints
>> 3. You can not defer an index
>> 4. You can not disable an index (except FBI)
>> 5. Unique indexes have no equivalent to NORELY
>> 6. Unique indexes have no equivalent to NOVALIDATE

>
> Hm... I could easily agree if it were not for the "never". Or maybe
> you meant "never build a unique index if you want to enforce a business
> constraint"?
>
> Would you even use a unique constraint if the index does not reflect a
> business need but is just a performance enhancement measure?
>
> Kind regards
>
> robert

"Never" is a bit extreme ... but I can't think of a reason to use one.

I've yet to see any value in a unique index that would make me choose it over a unique constraint. If someone can knows of one I'd like to see it and a demo that shows how it is superior.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Oct 17 2006 - 12:46:31 CDT