Autonumbers as primary keys?

[Johan]

Hi there,

I have some questions regarding primary keys. I wonder what the
general opinion about autonumbers are? Are they widely used?, and
what are some advantages, disadvantages to this?

My understanding is that the use of "intelligent" keys should be
avoided, keys that has any relation to the daily business, in a way to
avoid that the value would ever change. The problem is that I am
developing a database and have basically been forbidden to use
autonumbers as primary keys. Because of this, no primary keys have
been defined yet and data have already been entered. I am now
concerned that when the keys are defined, we will have further
problems when trying to impose the relationships.

I would highly appreciate any kind of input in this matter, thanks for
your time. Johan

 

[Duane Hookom]

I always use Autonumber primary keys on all tables. I have never experienced
any issues with doing this.

 

[Tim Ferguson]

(e-mail address removed) (Johan) wrote in

The problem is that I am
developing a database and have basically been forbidden to use
autonumbers as primary keys.

Forbidden by whom?

Autonumbers are certainly a convenient way of allocating a unique number to
each record with -- in normal operation -- negligible chance of
duplication. They are certainly not the only way, however, and there is
plenty of code around on the web for constructing other automatic numbers.
And there are problems with ANs: for example it is not always easy to find
out the number of the record you have just created.

And I am one of the camp who beleive that real life keys are still better
than artificial ones -- as long as they are genuinely stable and unique,
and there is the rub. Many suggestions do not live up to these attributes:
for example Social Security Numbers, anything to do with Names or Dates,
and so on. Good candidates would be page numbers from an Invoice book
(although make sure you have a method for catching when a new book is
started!), someone else's successful computer system (hospital record
numbers) and so on. As you can see, though, even these examples can be shot
down.

There is a great deal of religion surrounding the use of ANs, but I don't
know of anyone who knows Access who would suggest "forbidding" their use.

HTH


Tim F

 

[John Vinson]

I have some questions regarding primary keys. I wonder what the
general opinion about autonumbers are? Are they widely used?, and
what are some advantages, disadvantages to this?

They are widely used, but as noted elsethread there are varying
opinions on them. They are convenient, easy to implement, and
generally reliable (excluding a few known bugs). They aren't suitable
for human consumption however: they will always have gaps and can
become random, for instance if you Replicate the database; in
addition, if there is a good "natural" key (a field or small number of
fields which are unique, stable, and short) adding an Autonumber is
like "honey as a sauce to sugar"; it can even be detrimental in that
it makes the record unique whether the data within the record is
really unique or not.

My understanding is that the use of "intelligent" keys should be
avoided, keys that has any relation to the daily business, in a way to
avoid that the value would ever change.

Well... you *can* set Cascade Updates, but at the cost of a marked
performance hit and risk of database bloating. Primary Keys should
indeed change *very rarely*, but *never* is a bit too strong.

The problem is that I am
developing a database and have basically been forbidden to use
autonumbers as primary keys. Because of this, no primary keys have
been defined yet and data have already been entered. I am now
concerned that when the keys are defined, we will have further
problems when trying to impose the relationships.

You may want to consider "rolling your own" autonumber. There are
endless discussions here concerning techniques to programmatically
generate a unique sequential numeric (or text) ID using VBA code. If
this will meet the objections of those who have forbidden autonumbers
you may have an out!