"JOG" wrote in message
...
On Jan 27, 2:09 am, "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.

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.

Not exactly. Artificial key values are simply names assigned to individuals
in the Universe of Discourse. I would think that it should be possible to
have many different names for the same thing: considering the fact that
there are a great many different languages, there must therefore be a great
many words for each thing.

* 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.

I wouldn't call it nonsense. It is not necessary that every property that
an individual exemplifies be represented in the database--only those
properties that are relevant to the problem at hand need be included. In
that event, if a particular individual is assigned a name at t1, and then if
the values for all of the properties that are relevant to the problem at
hand at t1 are compared to those from the individual with the same name at
t2, it is possible for all of those properties to be different. That isn't
nonsense, it just is, given the inherent incompleteness of the information
in the database.

* 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.


You left one out. In a table that has multiple natural keys, when an
artificial key is added, which key values are its values surrogates for?

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.


James A. Fortune