Re: Difference: Uniq. Constraint & Index ?

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

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown