Objective 1.2 Manage relationships and keys
- 1.2.1 Create and modify relationships
- 1.2.2 Set key fields
OBJECTIVE 1.2: Contents
1.2.1 Create and modify relationships
Create and modify relationships
When you create a table relationship, the type of the relationship depends on the data that the tables contain and how that data is related. Tables can have the following types of relationships:
One-to-many In this relationship, any one record in the first table can be related to many records in the second table (for example, one customer can place many orders), but any record in the second table (an order) is related to only one record in the first table (for example, each order is placed by a single customer).
One-to-one In a one-to-one relationship, each record in the first table is related to only one record in the second table. You can use a one-to-one relationship to maintain a separate table that defines and stores fields for data that you don’t refer to regularly or that you want to keep more confidential. For example, in an Employees table, you can store general employee information such as first and last name, department, job title, and building and office location. In separate EmployeeRating and EmployeeCompensation tables, you can store performance ratings and compensation data—information that you want only certain people or groups to use. Each record in the Employees table has a single matching record in the table for ratings or compensation.
Many-to-many An Orders table and a Products table have a many-to-many relationship because each record in the Orders table can have many matching records in the Products table, and each record in the Products table can have many matching order records. You can’t define a many-to-many relationship directly. Instead, you need to create a linking table (also known as a junction table) to create two one-to-many relationships. The linking table includes the primary key fields from both the other tables.
Tip: In the Northwind sample database that comes with Access, the Order Details table is a linking table.
When you create a relationship, Access displays the Edit Relationships dialog box. If Access detects matching fields in the tables (for example, if each table has a field named CustomerID), Access displays these fields in the Table/Query and Related Table/Query lists. You can replace these default selections when you need to. The relationship type is indicated at the bottom of the dialog box.
Settings for a one-to-many relationship
See Also: For information about join types, see “3.1.6 Create multiple-table queries,” in “Objective 3.1: Create queries.”
The Edit Relationships dialog box includes several important options, such as the following:
Enforce Referential Integrity Referential integrity is used to prevent orphan records (records in one table with no matching record in a related table) and to maintain references between related tables. By using referential integrity, you ensure that no record in one table refers to a record in another table that doesn’t exist; for example, a record for a book cannot refer to an author if a record for that author does not exist. If you enforce referential integrity, Access does not allow operations that violate referential integrity rules for that relationship; for example, you can’t enter a customer ID in the Orders table if that customer ID does not exist in the Customers table. Also, you can’t delete records that reflect an existing relationship; for example, you can’t delete a customer record if order records for that customer exist.
Cascade options When you apply referential integrity to a relationship, you can choose one or both Cascade options:
• If Cascade Update Related Fields is selected, Access updates the foreign key for all related fields when you make a change to the primary record.
• If Cascade Delete Related Records is selected, Access deletes all related records when you delete a primary record. If you delete a customer, for example, Access also deletes all order records for that customer.
In a large database with a web of table relationships, you might need to refine the view of the relationships in the Relationships window. You can modify the content of the Relationships window in the following ways:
Hide a table to remove it from the window.
Select a set of tables you want to view.
View the tables with direct relationships for the selected table.
View all the relationships in the database.
Drag the table thumbnails to alter the arrangement of the window.
When you close the Relationships window, Access prompts you to save the current layout.
The Object Dependencies pane provides information about how one database object depends on others and how others depend on it. Although the dependencies displayed are not the same as table relationships, you can use the Object Dependencies pane to see, for example, which forms depend on the data or fields in a specific table.
Tip: The Relationship Report command in the Tools group on the Relationships Tools Design tool tab produces a printable report of the current layout in the Relationships window.
To open the Relationships window
On the Database Tools tab, in the Relationships group, click Relationships.
To display tables in the Relationships window
1. On the Design tool tab, in the Relationships group, click Show Table.
2. In the Show Table dialog box, select the tables, and then click Add.
Tip: Hold down the Ctrl key to select multiple items.
To remove tables from the Relationships window
In the Relationships window, do either of the following:
• To remove one table, click the table to select it. Then on the Design tool tab, in the Relationships group, click Hide Table.
• To remove all tables, on the Design tool tab, in the Tools group, click Clear Layout.
To display relationships in the Relationships window
In the Relationships window, do either of the following:
• To display all direct relationships of a specific table, click the table to select it. Then on the Design tool tab, in the Relationships group, click Direct Relationships.
• To display all relationships in the database, on the Design tool tab, in the Relationships group, click All Relationships.
To view object dependencies
1. In the Navigation Pane, select the database object whose dependencies you want to view.
2. On the Database Tools tab, in the Relationships group, click Object Dependencies.
3. In the message box informing you that Access needs to update dependency information, click OK.
4. In the Object Dependencies pane, click Objects that depend on me or Objects that I depend on.
To create a table relationship
1. Open the Relationships window. If the tables you want to create relationships between aren’t displayed in the Relationships window, add them.
2. Drag the linking field from the first table (the “one” table in a one-to-many relationship) to the second table (the “many” table).
3. In the Edit Relationships dialog box that opens, do the following:
a. Ensure that the linking fields are selected in the Table/Query and Related Table/Query lists.
b. If you want to enforce referential integrity for this relationship, select the Enforce Referential Integrity check box.
c. If you enforce referential integrity, you can also do one or both of the following:
If you want Access to update related fields when you change the primary record, select the Cascade Update Related Fields check box.
If you want Access to delete related records when you delete the primary record, select the Cascade Delete Related Records check box.
d. Click Create to establish the relationship and close the dialog box.
To modify a relationship
1. Open the Relationships window and do either of the following:
• Click the relationship line between two tables. Then on the Design tool tab, in the Tools group, click Edit Relationship.
• Right-click the relationship line between two tables, and then click Edit Relationship.
2. In the Edit Relationships dialog box, modify the table or query selections, the join type, or the options, and then click OK.