Thank you for the reply. I think the person (or people) who argue for a
"natural" PK have made the point that the PK does not need to be used in the
relationships. In other words, a unique field can be on the one side of a
one-to-many, and the PK can be a separate entity. The point of a PK, in
that way of thinking, is to guard against duplication (e.g. entering the
same person twice) in a way that would not be possible if an individual
field is set to Indexed (No Duplicates).
I would rather use a form's Before Update event for such checking. In
another posting I referred to the real-life situation of a mother and
daughter with the same name in a family-run business. Even if it is
uncommon, it is certainly at least possible. I would want the option of
allowing the apparent duplication in at least some cases. I would need to
reinvent the PK if I was using a multi-field PK for that purpose and
discovered an apparent duplication.
I am inclined to use autonumber or some other fixed value (e.g. Invoice
Number) as the PK. I use code to contrive an incrementing value in the case
of the aforementioned Invoice Number and other situations in which the
number is part of what the user sees. I don't see a need for two guaranteed
unique identifiers in one record. Even in the case of Employee ID numbers I
lean toward a meaningless number, because if the company changes hands they
may switch to a different numbering system for Employee IDs. However, I am
interested in hearing other views on the subject. In the course of the
dialogue I am learning some new things, even if I don't decide to change to
multi-field PKs as a matter of course.

"Amy Blankenship" wrote in message
...

"BruceM" wrote in message
...
What would you do to guarantee uniqueness in a Contacts table or some
such involving names and addresses, in light of the fact that names and
addresses are subject to change?

SQL underlies Access queries. The design grid is a sort of SQL GUI (as I
understand it). So I think you're saying that displayed order (e.g.
sorted by last name) is not what you are talking about when you talk
about physical order. If I understand, you are saying that the structure
of the index determines the order on the disk, not the order in the table
when it is viewed directly.

I have a database that includes an Employees table. The primary key is
the EmployeeID. With it to do over again I might have used something
else, because it is at least possible that they will one day change the
format of EmployeeID, which is just a sequential 4-digit number.

Which is why most people use completely meaningless Autonumber fields as
primary keys. Because you can't change the value, format, or anything
else of a field that is currently being used as a primary key. Also the
autonumber field will usually have a smaller size (on disk, no less) than
a more meaningful key. Therefore, if you are using it in a relationship
or relationships, the other tables will have to store less information
when they are referring to that primary key of this table.

So, for instance, if you had an employeeID that was an autonumber, all of
the other tables that refer to your EmployeeID would have saved 11 bytes
every time they had a foreign key to your employee table, and you could
have stored what is now your employeeID primary key just once, for a total
of just the one 15 byte storage of the employeeID string. This is the
whole point of normalization. Anything that is actually used as data
should just be stored once, with the smallest possible reference to it
from other places that need to relate to the base data.

More than likely you'll eventually have to move to an Autonumber primary
key there for the above listed reasons. Most of us encounter this
situation at least once, and from that point forward we use Autonumber
primary keys, since fixing the problem once it has developed is much more
of a pain than preventing it.

Hope this clarifies;

-Amy