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

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 15 Feb 2006 13:31:41 GMT
Message-ID: <13GIf.30286$VV4.311287@ursa-nb00s0.nbnet.nb.ca>

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.

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

In DB2, a unique index mixes one logical constraint (uniqueness) with a physical structure (index).

What part of "NULL is an abomination" do you not understand?

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

>>and possibly by limiting the declarable foreign key
>>constraints to reference a single primary key per table.

>
> This is not correct. In SQL, a FK can reference any unique constraint
> (incl. the primary key, of course).

Okay, good. I was not sure. That certainly makes it less punishing.

>>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. Received on Wed Feb 15 2006 - 07:31:41 CST