Re: Difference: Uniq. Constraint & Index ?

On 19.10.2006 17:00, Brian Peasland wrote:
>> 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.

> 
> If the index had the combination of both columns, one being the primary 
> key column, then basically, we have just created a larger primary key 
> which is not the minimal primary key.

The primary key was not touched in this case. An additional index was created solely to speed up lookups.

 > While the business rule was not

> explicitly stated that the combination of FOO_ID and FOO_ITEM is a 
> unique set of values, the business rule does explicitly state that 
> FOO_ID must be unique, and must be the PRIMARY KEY for this table. 

Right.

> Adding more columns to the primary key column(s), in essence, makes the 
> entire row the primary key. So we have created an *implicit* business 
> rule here stating that the entire row is UNIQUE.

I do not follow you there as it stretches the term "business rule" too far IMHO. To me a business rule is a rule that has is coming from the business layer, i.e. "foos must be uniquely identified by a number" and similar. The fact that the larger key's data is unique is just a logical consequence from the nature of the columns (i.e. one unique column is sufficient to make all sets of columns that contain it unique). And the larger key was not created for any business reasons other than performance tuning.

Kind regards

        robert Received on Thu Oct 19 2006 - 10:37:15 CDT