Building Table Relationships

In Access table relationships bring important information together so you can analyze important data and create a strong Access database.

Understanding the relationship map

The relationship map lists all of the tables that were selected to relate, as well as all of the fields that were previously set up for that table. Notice that the first field has a key icon next to it. This is the primary key for the table.

Primary KeysPrimary Keys

Primary and foreign keys

A primary key is the first field in each table of the database. You may recall that this field auto-numbers by default, so every record in the table has its own unique number to identify it. Access uses this number to quickly pull information together when you run queries or reports, which are covered later.

In the example above, the primary key for the Customers table is Customer ID, the primary key for the Orders table is Order ID, and the primary key for the Books table is Book ID.

A foreign key is a field that is the primary field in its own table but that shows up in another table. If you look closely at the Orders table, the fields Customer ID and Book ID appear there, as well as in their own respective tables. These fields are the primary key in their own tables, but in the Orders table, they are considered foreign keys.

Foreign KeysForeign Keys