Re: Philosophical question on primary keys

  • From: Vladimir Andreev <vandreev@xxxxxxxxx>
  • To: toon.koppelaars@xxxxxxxxxxx
  • 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
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

> 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

Other related posts: