Database Reference
In-Depth Information
Primary Key
button
132
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
Referential Integrity
In the relational model you have examined thus far, you have created the relationships between tables by
having 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 could not be sure that the matching field
names indicated 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. How-
ever, 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.
