Re: Philosophical question on primary keys
Date: Tue, 25 Aug 2009 19:08:15 +0200
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.
On Fri, Aug 7, 2009 at 21:24, Toon Koppelaars <toon.koppelaars_at_rulegen.com>wrote:
> 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.