Re: Philosophical question on primary keys

I've always thought that the ability to use a non-unique index to enforce a unique constraint is a necessary and possibly beneficial side effect of the ability to define deferrable unique constraints: there is simply no way to store a duplicate key in a unique index structure, even only for the life of a transaction. Why the non-unique requirement applies also to indexes supporting non-deferrable constraints (afaik, deferrability must be specified at constraint definition
time<http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#i1002273>) is still a mystery to me, though. Maybe it was more convenient to code it that way.

Cheers!
Flado

On Fri, Aug 7, 2009 at 21:24, Toon Koppelaars <toon.koppelaars_at_rulegen.com>wrote:

> Jay,
>
> Are you aware of the feature that a non-unique index can be used to enforce
> a primary key, whose columns are a prefix part of the indexed columns?
>
> Say table T, with columns A, B and C.
> Design-wise let A be the primary key.
> You can create an index (non-unique*!) on columns A,B and then add a
> primary key of A only.
> It will use the non-unique index to enforce the key.
>
> (*!) Now here's the *strange* thing, afaik it is required that this index
> be non-unique. Don't ask me why. Beats the heck out of me, since a proper
> subset of the indexed columns constitute a key of the table. If you ask me
> someone at Oracle wasn't paying attention when introducing this feature.
>
> Toon
>
>

--
http://www.freelists.org/webpage/oracle-l