About relationships in an Access database (MDB)

After you've set up different tables for each subject in your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) between your tables. After you've done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from four tables:

Orders form displaying related information from five tables at once

Callout 1 The Customers table

Callout 2 The Orders table

Callout 3 The Products table

Callout 4 The Order Details table

The customer name in the Bill To box is retrieved from the Customers table, the Order ID and the Order Date from the Orders table, the Product from the Products table, and the Unit Price and Quantity from the Order Details table. These tables are linked to each other in a variety of ways to bring information from each into the form.

The following sections explain in greater detail the types of relationships that are available, how they work together in the form, and what you should know about using relationships to make tables give you exactly the information you want from your database.

ShowHow relationships work

In the previous example, the fields in four tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) from one table, which provides a unique identifier for each record, and a foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) in the other table. For example, employees can be associated with orders they're responsible for by creating a relationship between the EmployeeID fields.

EmployeeID used as primary key in Employees table and foreign key in Orders table.

Callout 1 EmployeeID appears in both tables—-as a primary key ...

Callout 2... and as a foreign key.

ShowA one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.

A record in Suppliers table with more than one related record in Products table

Callout 1 One supplier ...

Callout 2... can supply more than one product ...

Callout 3... but each product has only one supplier.

ShowA many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) consists of two fields — the foreign keys (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.

Foreign keys in Order Details table and matching primary keys in Orders table and Products table

Callout 1 Primary key from the Orders table

Callout 2 Primary key from the Products table

Callout 3 One order can have many products ...

Callout 4... and each product can appear on many orders.

ShowA one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table.

Every record in the Employees table can have only one matching record in the Soccer Players table.

Callout 1 Each soccer player has one matching record in the Employees table.

Callout 2 This set of values is a subset of the EmployeeID field and the Employees table.

ShowAbout defining relationships

The kind of relationship that Microsoft Access creates depends on how the related fields are defined:

You can also create a relationship between a table and itself. This is useful in situations where you need to perform a Lookup within the same table. In the Employees table, for example, you can define a relationship between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

 Note   If you drag a field that isn't a primary key and doesn't have a unique index to another field that isn't a primary key and doesn't have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) line between the tables, but referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.) won't be enforced, and there's no guarantee that records are unique in either table.

ShowReferential integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

The following rules apply when you use referential integrity:

ShowCascading updates and deletes

For relationships in which referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you add, update, or delete records.) is enforced, you can specify whether you want Microsoft Access to automatically cascade update (cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.) and cascade delete (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) values in a primary table (primary table: The "one" side of two related tables in a one-to-many relationship. A primary table should have a primary key and each record should be unique.), Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you select the Cascade Update Related Fields check box when defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship isn't broken. Microsoft Access cascades updates without displaying any message.

 Note   If the primary key in the primary table is an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field, setting the Cascade Update Related Fields check box will have no effect, because you can't change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when defining a relationship, any time you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), Microsoft Access automatically deletes the records in related tables without displaying a warning.

 
 
Applies to:
Access 2003