"James A. Fortune" wrote in message
...
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 :-).


Well, that's just dumb. Checks in code can reduce database round-trips, and
therefore can improve performance, but are not and cannot be a substitute
for constraints on the tables. It is the constraints on the tables that
keeps garbage out of 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.

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.


An extra join may be needed if the natural key from the parent table is used
in a restrict clause. If all you have is the artificial key from the parent
table, then you have to join in order to access the natural key columns.
With natural keys, the natural key values from the parent table also appear
in the child table, so there isn't any need to join. Bottom line: joins of
artificial keys are typically faster than joins of natural keys due to the
size of the comparands, but with natural keys, fewer joins may be needed..


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