Re: Separate PK in Jxn Tbl?

From: James A. Fortune <MPAPoster_at_FortuneJames.com>
Date: Sat, 26 Jan 2008 22:06:39 -0500
Message-ID: <#xDvuHJYIHA.5472_at_TK2MSFTNGP06.phx.gbl>


JOG wrote:
> On Jan 27, 2:09 am, "James A. Fortune" <MPAPos..._at_FortuneJames.com>
> wrote:
>

>>Marshall wrote:
>>
>>>On Jan 26, 4:26 am, "David Cressey" <cresse..._at_verizon.net> wrote:
>>
>>>>When you want to delete an entry form a junction table,  you almost always
>>>>know the two FKs that uniquely determine the entry to be deleted.  You
>>>>almost never know the value of the superflous surrogate key.  So it's simple
>>>>to use the two FK's as the criterion for deletion than it is to look up the
>>>>ID field,  and then use that as the basis for deletion.
>>
>>>Yes, exactly.
>>
>>>One of the greatest benefits, and one of the fundamental
>>>differences between how SQL treats data and how
>>>(most) conventional programming languages treat data
>>>is that in SQL we specify data by its value, instead of by
>>>location. I often observe that superfluous keys in the field
>>>are an attempt to make SQL data have an address, to
>>>make it behave the way the programmer's mental model
>>>(perhaps influenced by years of using pointers) does.
>>
>>>Marshall
>>
>>Personally, I don't take the natural keys out either, so they can still
>>be used for the deletion.  The thought of giving the SQL data an address
>>and following a programmer's mental model did not enter into my thinking
>>at all.  I am not trying to give the data an order either.  You've been
>>listening to Celko too much.  Because of his overall manner, which I
>>find quite offensive, I don't even want to listen to him when he's right
>>:-).  Is the AutoNumber primary key a denormalization of the schema?
>>Yes.  Is it added for a reason?  Yes again.  I'm still waiting for a
>>cogent reason for me to go to using natural keys.

>
>
> * Artificial keys allow you to enter the exact same statement of fact
> twice. This would simply be nonsense.
> * Artificial keys allows a tuple at t1 and a tuple at t2 to be
> corresponded to each other, even if they don't have a _single_
> attribute from the real world in common. This would also simply be
> nonsense.

Access programmers use forms to interact with the data. If I follow Jamie's advice and constrain the data at both the table level and in code, then your points make more sense. Right now, they're just arguments for me not to constrain the data at the table level because the reasons you gave might make natural keys preferable in that situation :-).

> * Referencing an artificial key in a child table can complicates
> queries - and not just with a longer restrict clause, but with a whole
> extra join that may well have been unrequired if a natural key had
> been used.

I don't agree with that point. The child table can contain the AutoNumber primary key from the main table as a foreign key if desired.   I don't see how using the natural key fields requires less joins than that. Maybe an example would help me understand what you mean.

>
> So not one, but three cogent reasons of the top of my head. I wouldn't
> say there are never cases when an artificial key is useful, but they
> certainly shouldn't be hidden, and adding them blindly to every
> relation is surely just a bit silly. Regards, J.

Did I imply that that's what I do?

James A. Fortune
MPAPoster_at_FortuneJames.com Received on Sun Jan 27 2008 - 04:06:39 CET

Original text of this message