Creating Relationships Between Tables

Relationships can be tricky, both in real life and in Access databases. Many

beginners make the mistake of creating relationships willy-nilly, without

thinking about the appropriate match-ups. Bad relationships are worse than

none at all — again, both in life and in databases.

The most common type of relationship is a one-to-many relationship. (Here’s

where the real-life relationships metaphor breaks down.) A one-to-many rela-

tionship in Access connects the primary key field in one table (that’s the “one”

side) with a field in another table that stores the exact same type of data but

isn’t unique to each record (that’s the “many” side, also called the foreign key

field). For example, the ID field in the Customers table might have a relation-

ship to the Customer ID field in the Orders table. Look back at Figure 13-3,

which shows relationships between an Orders table and two other tables.

The purpose of a relationship is to connect the data between the tables.

With the aforementioned relationship, the Orders table can reference people

from the Customers table easily and efficiently. A relationship can option-

ally have referential integrity enforced. That means that Access won’t allow

entries in the field on the “many” side that don’t have corresponding entries

on the “one” side. In the aforementioned example, if referential integrity were

enforced, you wouldn’t be able to create orders in the Orders table for non-

existent customers (that is, people who don’t appear in the Customers table).

Referential integrity has two options: Cascade Update and Cascade Delete.

Cascade Update changes the entry in the foreign key table when the entry

in the primary key table changes. For example, if a customer’s ID number

changes in the Customers table, her ID will be automatically updated for each

of her records in the Orders table.

Cascade Delete deletes the entry in the foreign key table when the entry in

the primary key table is deleted. For example, suppose a customer is removed

from the Customers table for some reason (for example, the customer dies,

or asks to be removed). If Cascade Delete is enabled, all records in the Orders

table from previous orders that customer placed would be deleted.