Re: Separate PK in Jxn Tbl?

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:24e63bab-1720-4762-87a2-e1bcc76c130b_at_y5g2000hsf.googlegroups.com...
> On Jan 26, 6:09 pm, "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.
>>
>> Personally, I don't take the natural keys out either, so they
>> can still be used for the deletion.
>
> Since natural keys are data that needs to be managed, this
> is an unsurprising claim. I don't throw out the data I'm supposed
> to be managing either. Also consider this thread is about junction
> tables. Imagine what you would have left if you threw out the
> natural key of a junction table: nothing! Just the capriciously
> introduced surrogate key in a table by its lonesome self.
>
> Perhaps I've just stumbled into a sanity check for table
> design: every projection of a table should have a meaningful
> predicate. (Do I hear JOG's ears pricking up?) In a junction
> table in which a surrogate key has been introduced, what's
> the meaning of the projection of the table over the s.k. column?
> Oops!
>

I think you're jumping the gun, Marshall. Since a surrogate key value is just a name, what a projection over the s.k. column would leave you with is a set of names, and the only meaning that can be derived from each element is that there is an individual that has been so named and due to domain closure that that individual actually exists.

>
>> The thought of giving the SQL data an address and
>> following a programmer's mental model did not enter
>> into my thinking at all.
>
> Your not being consciously aware of it doesn't mean
> it didn't happen. The mind doesn't work like that.
>
>
>> You've been listening to Celko too much.
>
> Who? ;-)
>
>
> Marshall
Received on Sun Jan 27 2008 - 19:56:52 CET