Re: Philosophical question on primary keys

From: Vladimir Andreev <>
Date: Tue, 25 Aug 2009 19:08:15 +0200
Message-ID: <>


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

Received on Tue Aug 25 2009 - 12:08:15 CDT

Original text of this message