Chapter 3: Designing Bulletproof Databases
117
The benefits of a primary key
Have you ever placed an order with a company for the first time and then decided the next day to
increase your order? When you call the people at the order desk, they may ask you for your cus-
tomer number. You tell them that you don’t know your customer number. Next, they ask you for
some other information — generally, your zip code and last name. Then, as they narrow down the
list of customers, they ask your address. Once they’ve located you in their database, they can tell
you your customer number. Some businesses use phone numbers or e-mail addresses as starting
points when searching for customer records.Cross-Reference
Primary and foreign keys are discussed in Chapter 1, but, because these concepts are so important in database
applications, they are covered again in this chapter.
Database systems usually have more than one table, and the tables are related in some manner. For
example, in the Collectible Mini Cars database, tblCustomers and tblSales are related to
each other through the CustomerID field. Because each customer is one person, you only need
one record in tblCustomers.Each customer can make many purchases, however, which means you need to set up a second
table to hold information about each sale — tblSales. Again, each invoice is one sale (on a spe-
cific day at a specific time). CustomerID is used to relate the customer to the sales.The primary key in the parent table (CustomerID in tblCustomers) is related to a foreign key in
the child table (the CustomersID field in the tblSales table).Besides being a common link field between tables, the primary key field in an Access database
table has these advantages:l Primary key fields are always indexed, greatly speeding up queries, searches, and sorts
that involve the primary key field.
l Access forces you to enter a value (or automatically provides a value, in the case of
AutoNumber fields) every time you add a record to the table. You’re guaranteed that your
database tables conform to the rules of referential integrity.l (^) As you add new records to a table, Access checks for duplicate primary key values and
prevents duplicates entries, thus maintaining data integrity.
l (^) By default, Access displays your data in primary key order.
Tip
An index is a special internal file that is created to put the records in a table in some specific order. For exam-
ple, the primary key field in the tblCustomers table is an index that puts the records in order by
CustomerID field. Using an indexed table, Access using the index to quickly find record within the table.