On Jan 24, 10:24 am, JOG wrote:
An actual example I experience springs to mind - I have witnessed a
database where student projects were recorded via a schema of Project
Partners:{id:autonumber, RoleAerson, RoleBerson}, with PK(id).
None of the partnerships were aware of any "id" in the real world, and
simply submitted their partnership choices on paper to admin. A
clerical error resulted in 2/3 of the data being entered twice, which
left a lot of people flapping about the number of markers required
until the error was found. If the schema had used the natural {RoleA,
RoleB} key there would have been no issue.

My experience, also. That's not to say that everyone who uses
autonumber PKs neglects to put constraints on alternate/candidate keys
but I do encounter autonumber problems frequently i.e. tables where
the only unique constraint/index is a PK on the autonumber column,
which actually *facilitates* the inclusion of duplicates by giving the
developer a false sense of security: "I've added a PK, I've done my
bit." Being able to uniquely identify duplicates is of no comfort to
me.

Who do we have to blame for this state of affairs? The Access
interface, IMO. If you omit to define the PK when creating a table in
Design View, upon saving you get prompted to add a primary key (good)
but instead of providing a way to choose existing columns, the offer
is to add an autonumber. If you haven't got a key to begin with,
autonumber cannot provide you with one unless you expose it to end
users which everyone says you shouldn't do (everyone except Tony
Toews, naturally g). The message even asserts that you need a PK to
be able to create Relationships that reference the table which isn't
true of Access Relationships (which are so vague they can even be
based on columns of different respective data types) nor Jet foreign
keys (which can be based on a unique constraint/index). BTW in Access
2007 you now just get given an autonumber PK by default, which you
would then have to go to the trouble to remove.

it looks like Tony's reasons are secret, and may only be gleaned
from a romantic evening of fine wine and barry white.

Hmm, I once tried this with Tony [TIC]: I spent hours cooking him his
favourite dish, getting the seasoning just right. And what does he do
when we've sat down? He picks up the salt and, just as he was about to
apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't
you going to test it for seasoning first? I think you'll find it is
just fine." He replied, "I always add salt to my food. Why? No
particular good reason. One of my rules is that all food needs extra
salt." I was about to protest when he added, "You don't like my rules?
Tough. I don't care." Well, you can imagine how hurt I was, reader.
Not as much as Tony after I'd thrown him off the balcony, though.

Jamie.

--