But if you can't generate a key from the data, before adding an
autonumber, then you shouldn't create the new record anyway. Using an
autonumber just hides the fact that your data is bad. There are lots of
times when you really don't have any alternative to using some sort of
artificial identifier, but adding one where it isn't necessary just adds
one more thing that can go wrong. If you do add one you still have to do
that uniqueness check on the real data.

Almost no knowledge is a very stupid thing.

I really must get my web site fixed so I can just post a pointer to this
stuff. I keep having to repeat it in comp.databases.theory too.

From a theoretical standpoint my argument over there has always been
that it's fine to use an artificial key provided you understand the
problems it can introduce, and you are prepared to deal with them.

First remember why you need a key, it's what you use to identify one
particular record in the table. That's why it has to be unique, but
uniqueness is not the only thing required. Not only does the key have to
be unique, you have to know exactly which unique key will retrieve data
about each object that the record describes. There has to be a precisely
defined 1:1 mapping between the real-world objects and the data that is
supposed to describe them. If you know the value of the key you should
be able to tell unambiguously which object it refers to.

The problem with artificial keys is that there is no intrinsic
connection between the value of the key and the identity of the object
that it is supposed to identify. It's up to the database designer to
manage that 1:1 mapping. It's usually done outside the database itself.
When the US government uses a sophisticated version of the autonumber
system to give each person a Social Security Number it hands each person
their number and tells them to remember it. That's one way of managing
that mapping.

If you use an artificial key in a database you take on the
responsibility for managing the mapping between key values and the
things they identify. When you create a new record you first have to
make sure that the object you want to describe isn't already in the
database. That often means that you have to take whatever information
you have about it and search the database to make sure that there isn't
already a record. If you only rely on the autonumber filed to ensure
uniqueness there is nothing stopping you from having three kids called
Amy 1, Amy 2 and Amy 3. But if you need to check for uniqueness why not
just give them unique names, and use their names as the key?

So the problem is that adding an artificial key doesn't save you any
processing time when you add a new record, it just adds one more thing
you need to do and one more thing that can break. And after you have
created your identifier someone has to make sure that this label you
have created remains attached to one-and-only-one object.

There are sometimes valid reasons for using artificial keys internally
to the database. When the fields that make up the natural key are large
it becomes inconvenient to use them as foreign keys in other tables, so
you create an ID field and use that instead. This is the type of
artificial key that you shouldn't tell the users about, because if you
do that you lose control over it. When disk space was expensive saving
disk space was important. Now, making systems simpler and more reliable
is likely to be more important than saving a few terabytes. So if you
can use natural keys without affecting performance too badly then I
think you should do so. Use artificial keys when you must, natural keys
when you can.