Why should an AutoNumber field NOT be used as a primary key? 1

[paulfla]

Hi everyone I'm pretty new to MS Access 2000 and I have a question. Why should an AutoNumber field NOT be used as a primary key? I ran across the following info below on a web site. Can you all enlighten me please.

Thanks so much in advance for your time.


Each table, that might be used as a master in a relationship with another table (detail table), should have a field that is a unique numeric identifier for each record. Typically this unique numeric identifier is an auto-number field in Access. Make sure it is set to long integer data type. An example would be Employee_ID in a table of employees. Make this field indexed (no duplicates).

Almost never use this auto-number field as the primary key of the table.


Aside: If you are thinking of hiring an Access programmer or consultant ask to see a screen shot of a relationship diagram from an Access database he/she has built. If you see an autonumber ID field in bold then ask them 'what will prevent two records with the same data getting into this table?' If they can't give you a good answer then don't hire them.

 

[Orion45]

There is a good reason why autokeys shouldn't be used as primary keys in access but what this guy said doesn't make any sense. Access will always create a new (unique)key every time a record is inserted. Access also does not allow autokeys to be manually created by users or code. The problem is when many users are inserting into the same table at once. There is the unlikely, but possible, chance that two users will try to insert a new record at the same time and because Jet SQL creates autokeys on the front end first, a duplicate key could get inserted into the table. Large scale applications use a form of record locking to prevent this but for small applications you don't really need to worry about this happening. Hope this clears things up. You can further prevent duplicates by using more than one primary key, usually called combined keys.

 

[spider8]

HI,
I usually don't use autonumbers as primary keys because, as the "aside" statement indicates, there usually is nothing to prevent a duplicate record from being entered in an autonumber as primary key situation. Since autonumber generates a new number every time a new record is entered, the primary key has nothing to check against previous records that were entered to see if there is duplicate information.
On the other hand, for example, if you use something like a social security number as a primary key, you would not be generating a new number at the time the record is entered, as autonumber does. If you are trying to enter a SS# that has already been entered for an employee, the PK should catch it and return an error message, and you will not end up with a duplicate record for that employee. And, of course, you should never have more than one SS# for any one employee. That's what makes it a good primary key candidate.
There are instances where I use autonumber, for example when I have a table of transactions, (say, financial transactions, for example, which can often contain duplicate information) and I simply need a field to act as a control number when I am using code to track user input.
Hope this helped somewhat,
Spider8

 

[Lightning]

Consider the following table and data

Table
Field        Type           
ID         Autonumber     Primary Key
ProductID  Text  (10)
Product    Text (50)
Price      Currency

Data
  ID  ProductID  Product      Price
   1     W001    Widgets      $0.05
   2     D002    DooHickeys   $0.20
   3     G001    Gadgets      $1.00
   4     W001    Widgets      $0.05

The data is complying with the table settings, but you can still duplicate data. If however, the Primary Key is set to the ProductID field, the duplicate data will result in an error message advising of duplicate primary keys.

Of course, there are times when using the Autonumber field as the Primary key doesn't matter. These tables are quite often tables on the MANY side of a One-to-Many relationship, where the PK is only there to speed up sorting and querying and the main identifier is a Foreign Key..

HTH
Lightning

 

[KornGeek]

I argue that if you have a single-user database, then autonumber actually makes a good candidate for primary key for tables of transactions (as mentioned above) or similar information. This is simply because it requires very little programming effort to implement the autonumber.

However, once the database goes multi-user, I have seen recommendations against using autonumber because it can (in rare instances) create duplicates.
Sleep is for people with no caffeine.

 

[johnnymac43]

I usually find that people who say that something should NEVER be done have a very narrow view of the world, and have not considered the endless possibilities of real life.

There are countless examples in the real world where it is both sensible and logical to have an autonumber as the primary key of the main table in an application. Many people and organisations record details of items or events that have no unique identifier. Consider the following examples:

> Calls to an emergency response service (where only Time is likely to be unique, and even that is not certain).

> Letters from people who are not 'clients' in the accepted sense (thus, not having a unique identifying number).

> Bottles of wine in a private cellar (a popular Access application).

> Recipes (the traditional, if over emphasised, example of home computing).

> etc. etc. etc.

Go through life with both eyes open, forever scanning for maximum advantage.

John

 

[Transcend]

Hey guys,

I usually use a SQL backend for my databases but even then i use something like an autonumber (identity seed).

You can always put a check in you code before you insert the data to check it doesn't already exist, this is useful when inserting descriptions. I guess the only thing this doesn't cater for is mispelling of words ...

Transcend