An Introduction to Primary Keys in an Access 2007 Database

Primary keys are an important component and concept in an Access 2007 database. Without primary keys, it is impossible to ensure that every record in your database represents a unique row. This can cause problems later on when you start making queries, creating reports, and developing relationships between and among multiple tables.

In an Access 2007 database, a primary key is a single field that is not unlike any other field you may have in your tables. Technically, it is possible for more than one field to do the job of uniquely identifying a record. However, these instances are rare so you need not worry about them now as you begin learning Access 2007.

Every table you create in your Access 2007 database should contain a primary key field. Primary keys uniquely identify every record in your database. This has several advantages but one of the most important is that your database will operate faster with, rather than without, primary keys.

This is especially true if you intend to index your database so you can make faster queries. Suppose, for example, that you have two customers in your database named “John Smith.” Without a primary key to uniquely identify each of these records, your database will not be able to tell the difference between the first John Smith and the second John Smith.

Your database may contain lots of information and you may think that it would be impossible for you to have two or more records that contain the exact same information. That may be true today, but in the future data will be added, deleted, appended, etc. into and from your database. Remember also that databases that allow users to change data have their information altered all the time by the user. You have no way of knowing what data may change from day to day, making it more likely that two or more records may contain the same data. It is also possible that two records in your database will contain no information for a short time. Again, without primary keys, Access 2007 will not be able to tell these records apart.

It doesn’t matter where you place a primary key in your table. It can be the first field, last field, or any field in between. Although Access 2007 doesn’t care where your primary key resides, you will find it much easier later on if you place your primary key in the first field of your table. This is especially true when you start creating relationships between and among tables.

By default, Access 2007 adds a primary key to every table you create. Even if you create a table in Design View without a primary key, Access 2007 will add a primary key to the table when you save the table. Access 2007’s default field name for a primary key is ID. If you desire, change this field name to anything you like. Something more descriptive may be more appropriate because “ID” could mean anything in most databases. Remember that you may not be the only person who uses this database so choose a name that makes it clear that the field contains a primary key.

There are some restrictions when creating primary keys. First, each table can only contain one primary key. There is no reason to uniquely identify a record with two keys because this would be redundant. Remember, a primary key identifies a record uniquely. There is no reason do this twice. Doing so would create problems later when you query or index the database.

Access 2007 allows for many field types such as memo, OLE object, and hyperlink. None of these field types can be used for a primary key. For example, you cannot use an embedded picture (OLE object) to uniquely identify a record. Nor can you use a hyperlink for the same purpose. Also, avoid using a yes/no field type for a primary key because then you would be restricted to only two records on the table, one record uniquely identified with a “yes” value and another record uniquely identified with a “no” value.

click to enlarge

Once in Design View, click the field name in your table that you want to use as the Primary Key field. On the Design View tab on the Ribbon, click on the PRIMARY KEY button which has a picture of a key on it (see Figure 1). That’s it. Your database now has a primary key associated with each of the records in your table.

Primary keys are a necessary element in any Access 2007 database. Although you can technically create a table that doesn’t contain a primary key, you will likely need them when you make queries, create reports, and develop table relationships. For now, allow Access 2007 to create primary keys automatically and manipulate them later when you know more about what you will be doing with your database. If you do create custom keys for your Access database, be sure to follow the rules to eliminate costly mistakes later.