Page 5 of 10PREVNEXT

Build relationships for a new Access 2007 database

The process of setting referential integrity

The relationship you created in the previous section doesn't become a one-to-many relationship until you set referential integrity. Referential integrity ensures that your primary and foreign key fields stay in synch whenever you add, change, or remove data. In turn, that keeps your data accurate.

Referential integrity also prevents orphaned records. Here's how that works: say you delete a supplier from your database. If you enforce referential integrity, then any data related to that supplier is also deleted. In other words, the deletion cascades through your data. However, if you don't enforce referential integrity, then the data related to that supplier references a parent value that no longer exists. Those records become "orphans," and using that data becomes much more difficult.

You set referential integrity by editing a relationship.

Callout 1 Close any open tables, click the Database Tools tab, and in the Show/Hide group, click Relationships.
Callout 2 Click the line that represents the relationship you want to edit. The line becomes thicker.
Callout 3 Right-click the line and click Edit Relationship. The Edit Relationship dialog box appears.
Callout 4 Select the Enforce Referential Integrity check box.
Callout 5 Optionally, you can change data in one place and have that change appear in other related data. To do so, select the Cascade Update Related Fields and Cascade Delete Related Records check boxes. When you finish, click OK.
Page 5 of 10PREVNEXT