Re: Difference: Uniq. Constraint & Index ?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 19 Oct 2006 10:23:22 +0200
Message-ID: <4poqvqFis4f9U1@individual.net>

On 18.10.2006 19:55, Brian Peasland wrote:

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

>
> Even assuming that the above is correct, if it is not a business rule,
> then why are we using a unique index? The fact that the column(s) should
> be unique *is* a business rule. Can you provide an example of a
> column(s) being unique that is not defined by a business rule? I can't.

I came across an example. Basically uniqueness was a side effect of the data being entered and the columns the index used. I think I roughly remember the basics: there was a table like

create table foo (

foo_id number primary key,
foo_item varchar2(100)
)

Now, there was a non unique index on foo_item. Since this table had many entries and there were many lookups to foo_id via foo_item the DBA suggested to add foo_id to the index as last column; that way lookups could be satisfied purely via that index and Oracle never needed to go to the table's pages. As a side effect of this, although foo_item is not unique, (foo_item, foo_id) is unique and the index was in fact made unique because of better storage characteristics.

That's roughly the story as I remember it.

Kind regards

robert Received on Thu Oct 19 2006 - 03:23:22 CDT