To clarify, I am not going to use a multi-field PK when there are other (and
simpler) means to guarantee uniqueness. If I have PartNumber and Process
(plating, welding, etc.) it makes sense to combine the two, since the
combination of Blade and Plating should appear just once. Combining
FirstName, MI, LastName, and a few address fields PLUS an autonumber or some
other kind of ID, then storing all of those fields in the Child table, makes
no sense to me. I can't believe that it is more efficient storing so much
redundant data then to have the index be based on an artificial number. It
has a lot to do with reading what others have written here (people who offer
a lot pragmatic and practical advice, and who have demonstrated again and
again their command of the program). EmployeeID (a four-digit number) works
quite well. I have no intention of bloating my database by storing several
other (and unnecessary) fields in the interest of a physical order that is
as arbitrary as any other way of arranging the data. Sometimes I need to
arrange Employee information by criteria other than LastName (by Department,
for instance, or by date). Even if I cared about physical order, one way of
ordering the records in my table is as good as another when I need to
arrange them in so many different ways in the course of using the database.
EmployeeID is as good a choice as any. Same with an autonumber.
"Destroys" is too strong a word. However, you will not be able to convince
me that continually updating multiple records and storing so much redundant
data improves anything in a situation where the unique ID number is part of
the record anyhow.
You will need to look elsewhere for a convert. Your thoughts have been
interesting, and have prompted me to investigate some new areas, but are not
going to inspire me to rework my basic approach of basing my PK on
uniqueness rather than on a particular physical order.

wrote in message
oups.com...

BruceM wrote:
I am not going to use a
multi-field PK.

Why? You seem to be basing this on 'gut feeling'. Remember PK =
clustered index (physical order), and shouldn't be confused with a
simply NOT NULL UNIQUE candidate key.

A name and address combination
is a poor choice for PK.

Remember PK = clustered index. If you have relevant queries which use
these columns then they stand a good chance of making an excellent PK.

Updating many records if somebody's address
changes makes no sense whatever. It destroys the whole point of using a
relational database.

No, it tells you that using an name + address doesn't make a very good
key (as you said earlier) but if it's all that you've got then you are
stuck with it (or change the system). You seem to be assuming a key
cannot change, again based on gut feeling alone. I ask again, why do
you think ON UPDATE CASCADE was invented?