How to Define Relationships Between Tables

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 theFieldSize 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 Tabledialog 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 Indexesdialog 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.

Back to the top

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 FieldSizeproperty 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.