Databases Reference
In-Depth Information
Primary Key
button
130
CustomerNum is
the primary key
FIGURE 4-15
Specifying a primary key in Access
If the primary key consists of more than one field, select the first field, press and hold down the Ctrl key,
and then click the other field or fields that make up the primary key. Clicking the Primary Key button adds
the key symbol to the row selectors of the primary key fields, as shown in Figure 4-16.
The combination of
OrderNum and PartNum
is the table's primary key
FIGURE 4-16
Specifying a primary key consisting of more than one field in Access
In the relational model you've examined thus far, you have created the relationships between tables by hav-
ing common fields in two or more tables. The relationship between sales reps and customers, for example, is
accomplished by including the primary key of the Rep table (RepNum) as a field in the Customer table.
This approach has several drawbacks. First, relationships are not very obvious. If you were not already
familiar with the relationships in the Premiere Products database, you would have to find the matching fields
in separate tables in order to locate the relationship. Even then, you couldn't be sure that the matching field
names indicate a relationship. Two fields having the same name could be just a coincidence—the fields
might have nothing to do with each other. Second, what if the primary key in the Rep table is named
RepNum but the corresponding field in the Customer table is named SlsrNo? Unless you are aware that these
two fields are identical, the relationship between customers and sales reps would not be clear. In a database
having as few tables and fields as the Premiere Products database, these problems might be manageable. But
picture a database that has 20 tables, each containing an average of 30 fields. As the number of tables and fields
increases, so do the potential problems.
