Defining relationships between tables in a Microsoft Access database

This article describes how to define relationships in a Microsoft Access database. It includes the following topics:

• What Are Table Relationships
• Types of Table Relationships
• One-To-Many Relationships
• Many-To-Many Relationships
• One-To-One Relationships


• How to Define Relationships Between Tables
• How to Define a One-To-Many or One-To-One Relationships
• How to Define a Many-To-Many Relationships


• Referential Integrity
• Cascading Updates and Deletes
• Join Types

What Are Table Relationships

In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table called Titles that stores information about each book, such as the book’s title, date of publication, and publisher. There is also information you might want to store about the publisher, such as the publisher's phone number, address, and zip code. If you were to store all of this information in the titles table, the publisher’s phone number would be duplicated for each title that the publisher prints.

A better solution is to store the publisher information only once in a separate table, Publishers. You would then put a pointer in the Titles table that references an entry in the Publishers table.

To make sure that your data is not out of sync, you can enforce referential integrity between the Titles and Publishers tables. Referential integrity relationships help ensure that information in one table matches information in another. For example, each title in the Titles table must be associated with a specific publisher in the Publishers table. A title cannot be added to the database for a publisher that does not exist in the database.

Types of Table Relationships

A relationship works by matching data in key columns, usually columns with the same name in both tables. In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. For example, sales can be associated with the specific titles sold by creating a relationship between the title_id column in the Titles table (the primary key) and the title_id column in the Sales table (the foreign key).

There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

One-To-Many Relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A. For example, the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each title comes from only one publisher.

A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint.

In Access, the primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B. For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s primary key).

One-To-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:

• Divide a table with many columns.
• Isolate part of a table for security reasons.
• Store data that is short-lived and could be easily deleted by simply deleting the table.
• Store information that applies only to a subset of the main table.

In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.

How to Define Relationships Between Tables

When you create a relationship between tables, the related fields do not have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.

How to Define a One-To-Many or One-To-One Relationships

To create a one-to-many or a one-to-one relationship, follow these steps:

1. Close any tables that you have open. You cannot create or modify relationships between open tables.
2. In Access 2002 or in Access 2003, follow these steps:
   1. Press F11 to switch to the Database window.
   2. On the Tools menu, click Relationships.
   In Access 2007, click Relationships in the Show/Hide group on the Database Tools tab.
3. If you have not yet defined any relationships in your database, the Show Table dialog box is automatically displayed. If you want to add the tables that you want to relate, but the Show Table dialog box is not displayed, click Show Table on the Relationships menu.
4. Double-click the names of the tables that you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.
5. Drag the field that you want to relate from one table to the related field in the other table. To drag multiple fields, press CTRL, click each field, and then drag them.
   
   In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table.
6. The Edit Relationships dialog box is displayed. Ensure that the field names displayed in the two columns are correct. You can change them if necessary.
   
   Set the relationship options if necessary. If you need information about a specific item in the Edit Relationships dialog box, click the question mark button, and then click the item. These options will be explained in detail later in this article.
7. Click Create to create the relationship.
8. Repeat steps 5 through 8 for each pair of tables that you want to relate.
   
   When you close the Edit Relationships dialog box, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships that you create are saved in the database.
   
   NOTE: You can create relationships in queries as well as tables. However, referential integrity is not enforced with queries.

How to Define a Many-To-Many Relationships

To create a many-to-many relationship, follow these steps:

1. Create the two tables that will have a many-to-many relationship.
2. Create a third table, called a junction table, and then add to the junction table new fields with the same definitions as the primary key fields from each of the other two tables. In the junction table, the primary key fields function as foreign keys. You can add other fields to the junction table, just as you can to any other table.
3. In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an TitleAuthors junction table, the primary key would be made up of the OrderID and ProductID fields.
   
   NOTE: To create a primary key, follow these steps:
   1. Open a table in Design view.
   2. Select the field or fields that you want to define as the primary key. To select one field, click the row selector for the desired field.
      
      To select multiple fields, hold down the CTRL key, and then click the row selector for each field.
   3. In Access 2002 or in Access 2003, click Primary Key on the toolbar.
      
      In Access 2007, click Primary Key in the Tools group on the Design tab.
      
      Note If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes dialog box, and then reorder the field names for the index named PrimaryKey.
4. Define a one-to-many relationship between each of the two primary tables and the junction table.

Referential 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 do not accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

• The matching field from the primary table is a primary key or has a unique index.
• The related fields have the same data type. There are two exceptions. An AutoNumber field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
• Both tables belong to the same Microsoft Access database. If the tables are linked tables, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity cannot be enforced for linked tables from databases in other formats.

The following rules apply when you use referential integrity:

• You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you cannot have an order that is assigned to a customer that does not exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
• You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
• You cannot change a primary key value in the primary table, if that record has related records. For example, you cannot change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.

Cascading Updates and Deletes

For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update or cascade delete 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 values in a primary table, Microsoft Access makes the necessary changes to related tables to preserve referential integrity.

If you click to select the Cascade Update Related Fields check box when you define a relationship, any time that 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 is not broken. Microsoft Access cascades updates without displaying any message.

NOTE: If the primary key in the primary table is an AutoNumber field, selecting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when you define a relationship, any time that 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, Microsoft Access automatically deletes the records in related tables without displaying a warning.

Join Types

There are three join types, as follows:

Option 1 defines an inner join. An inner join is a join where records from two tables are combined in a query's results only if values in the joined fields meet a specified condition. In a query, the default join is an inner join that selects records only if values in the joined fields match.

Option 2 defines a left outer join. A left outer join is a join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right.

Option 3 defines a right outer join. A right outer join is a join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left.