Part I: Access Building Blocks
78
Creating composite primary keys
You can designate a combination of fields to be used as a table’s primary key. Such keys are often
referred to as composite primary keys. As indicated in Figure 2.26, select the fields that you want to
include in the composite primary key, then click the key icon in the Tools ribbon tab. It helps, of
course, if the fields lie right next to each other in the table’s design.
FIGURE 2.26
Creating a composite primary key
Composite primary keys are primarily used when the developer strongly feels that a primary key
should be comprised of data that occurs naturally in the database. There was a time when all
developers were taught that every table should have a natural primary key (data that occurs natu-
rally in the table).
The reason that composite primary keys are seldom used these days is because developers have
come to realize that data is highly unpredictable. Even if your users promise that a combination of
certain fields will never be duplicated in the table, things have a way of turning out differently than
planned. Using a surrogate primary key (a surrogate primary key is a key field that does not natu-
rally occur in the table’s data, such as a Social Security Number or Employee ID), such as an
AutoNumber, separates the table’s design from the table’s data. The problem with natural primary
keys is that, eventually, given a large enough data set, the values of fields chosen as the table’s pri-
mary key are likely to be duplicated.
Furthermore, when using composite keys, maintaining relationships between tables becomes more
complicated because the fields comprising the primary key must be duplicated in all the tables
containing related data. Using composite keys simply adds to the complexity of the database with-
out adding stability, integrity, or other desirable features.
Indexing Access Tables
Data is rarely, if ever, entered into tables in a meaningful order. Usually, records are added to
tables in random order (with the exception of time-ordered data). For example, a busy order-entry
system will gather information on a number of different customer orders in a single day. Most
often, this data will be used to report orders for a single customer for billing purposes or for
extracting order quantities for inventory management. The records in the Orders table, however,