Identifying the Primary Key

(or the Key, and nothing but the Key)

The Primary Key is made up of a field or set of fields (Combination Key) that uniquely identify each record in the table. Ideal Primary keys include a social security number, customer number, invoice number, or order number, but what if your table doesn't include one of these great unique identifying numbers?

When deciding which fields to choose as a primary key, try to pick numeric value fields if possible.

If a numeric field is not possible, text-based keys do work, but you have to be careful setting them up since you don't want your database to be too difficult for the user to use.

You can create a combination or composite key that combines the values from multiple fields to form the primary key. An example is selecting the combination of FirstName, LastName, and CompanyName as a key, or FirstName, LastName, and BusinessTelephoneNumber may be a better key, since it is rare, but possible to have two people with the same names who work for the same company.

Finally, the least desirable option to create a primary key would be to use a computer generated number as a key. This would be a number that the database assigns automatically when the record is inserted (AutoNumber). If you have a table that you are tracking data that can be random, this will likely be your best choice. In addition, I would recommend that you set the option to random rather than incremental, as this will be useful if the database is ever used by more than one user.

The most important point to remember when selecting your Primary Key field is that for each record that you add to your database, the key data will be unique. If you have duplications, you will have errors.

Table: Using spaces in field names is perfectly acceptable in Microsoft Access, however if there is a possibility that you may want use the tables in another program in the future, such as SQL Server, a better option is to use a mixture of Uppercase and Lowercase characters instead.

Other databases may not be compatible with spaces within field names. You can easily add a Caption with spaces that will show up on your forms, queries, and reports.