Re: Do I need a PK on a join table?

From: Knut Stolze <stolze_at_de.ibm.com>
Date: Wed, 15 Feb 2006 15:41:42 +0100
Message-ID: <dsvej6$n10$1@lc03.rz.uni-jena.de>

Bob Badour wrote:

> Knut Stolze wrote:
>

>> Bob Badour wrote:
>> 
>>>SQL and DB2 confound the issue by mixing physical structures (indexes)
>>>with purely logical integrity constraints (uniqueness/keys), by allowing
>>>the abominable NULL,
>> 
>> I don't know where there is anything "mixed" and how NULL would fit into
>> this statement.  Could you possibly explain how you mean that?

>
> Sound data management as expressed by the principle of data independence
> suggests physical structures and logical constraints should remain
> strictly separate. See:
> http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter1/node15.html
>
> Indexes are physical structures.
> Keys are logical constraints.
> Uniqueness is a logical constraint.

Agreed, that's just basic knowledge.

> In DB2, a primary key mixes two logical constraints (candidate key,
> uniqueness) with a physical structure (index).

No, not really. Granted, DB2 creates a unique index to support the primary key/unique constraint. And that comes with logical/conceptual semantics. I think it would be nice if a unique index could not be created explicitly. But that's never going to change from how it is today.

> In DB2, uniqueness and candidate key are two logical constraints instead
> of one due to the abominable NULL.

With "uniqueness" your are referring to unique indexes not unique constraints, right?

>>>If you have a strong argument for wanting no index, declare a uniqueness
>>>constraint.
>> 
>> Which will be enforced by a unique index...

>
> In that case, DB2 forces one to have an index when one has a strong
> argument for not wanting it. That seems rather stupid to me.

Sure. But how "strong" could such an argument possibly be? I have not yet seen it in the real world.
Triggers would be a way to avoid the index. But then you can't use the concept of unique constraints.

-- 
Knut Stolze
DB2 Information Integration Development
IBM Germany

Received on Wed Feb 15 2006 - 08:41:42 CST