Re: Separate PK in Jxn Tbl?

From: David Cressey <>
Date: Mon, 28 Jan 2008 14:01:37 GMT
Message-ID: <5flnj.799$hr6.666_at_trndny04>

"Rob" <> wrote in message On Jan 22, 1:26 pm, "Neil" <> wrote:


I don't claim to know more about db theory than the cdt regulars, or to have more experience than the many practioners who have contributed to this thread.

I do know that the debate over relationship representations has been going on for years, neither side giving any ground. Some aspects of it are never addressed. For example, making the two foreign keys in a junction table a composite PRIMARY key prohibits NULL values from either key, but declaring a separate surrogate primary key together with a UNIQUE constraint on the two-foreign-key-composite-key does allow NULL values for either (or both!). This can be useful for representing unrelated entity tuples, either childless parents or orphans.


In the case of a junction table, this point is moot.

If both FK values are NULL, the entire row of the junction table can be omitted with no loss of information.

Childless parents will exist in the table that contains parents, where the id of the parent is not an FK, but a PK. Same pattern for orphans. The two tables can even be the same table. Received on Mon Jan 28 2008 - 15:01:37 CET

Original text of this message