Re: Difference: Uniq. Constraint & Index ?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 18 Oct 2006 10:46:36 +0200
Message-ID: <4pm7vcFjlkssU1@individual.net>

On 17.10.2006 19:46, DA Morgan wrote:

> 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?

> "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.

The basis for my question was that I've frequently read the rule of thumb "if it is a business constraint, use a DB constraint for enforcement - otherwise use an index". This seems a bit conflicting with your general preference of constraints. Or do you think this rule of thumb to be wrong?

Kind regards

        robert Received on Wed Oct 18 2006 - 03:46:36 CDT