Separate PK in Jxn Tbl?
On Jan 27, 6:16 pm, Marshall wrote:
On Jan 26, 6:09 pm, "James A. Fortune"
wrote:
Marshall wrote:
On Jan 26, 4:26 am, "David Cressey" 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?)
I have no idea what you mean.
Well not since my semantic-2000 buzzword detector imploded when
someone described "web 3.0" to me.
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!
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
|