Primary Keys

[Jeanette]

I'm a novice at access, and your opinion would be helpful. Is it ok to use autonumber as the primary key for all my tables. I have fifteen tables and I am using autonumber as the primary key in two tables the others have have created my own unique primary key field. Also, in a many to many table which is best as a primary key: creating a combination of the two primary keys from each table as the primary key in the many to many table, or creating a third autonumber field as the primary key. Thanks in advance.

 

[Vassago]

I try to stay away from autonumbers because I see many people have problems with them. I usually use something that will be unique for each record and that I don't want duplicated for a primary key. If no such field exists, then I create one be combining a few fields together. This uniquely identifies it and will allow me to carry this primary key to other tables if need be. Whereas, an autonumber probably could be repeated from one table to another.

 

[Pat Hartman]

In the popular press there are two schools of thinking. Those that always use artifical keys (autonumbers) and those that use natural keys when they exist. I belong to the latter. Use an autonumber when:
1. Your table does not contain an appropriate candidate key.
2. To create a unique key would require the use of more than three separate fields (Access allows 10, other RDBMS's allow more).

Relation tables fall into two classes. Those that can be uniquely defined with the two primary keys and those that can't. The first type are tables such as EmployeeSkills where the key would be EmployeeID plus SkillID. The second type are relations that need to be maintained over time. An example would be EmployeePositions. Here you would need the EmployeeID, PositionID, and a StartDate to uniquely define a row. History needs to be maintained and an employee may hold the same position during multiple timeframes. These tables for some reason seem to be more likely to have dependent tables and when that happens, I prefer to use an autonumber primary key and simply use a unique index to enforce the uniqueness of the "natural" key.