JOG wrote:
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.
* 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