MS Access —

Enforcing Referential Integrity

When you create a relationship between two tables, it is usually a good idea to enforce . So what does this technical-sounding phrase mean? Referential integrity keeps records in related fields valid and accurate. Referential integrity ensures that you don't accidentally change or delete related data in one table but not in the other. For example, say you were using two related Social Security fields to link two tables. Referential integrity would not allow you to change the Social Security number in one record without changing the Social Security number in the other related records.

Referential Integrity Prerequisites

Access is very picky about when you can set . You can only use referential integrity when all of the following conditions are met:

  • One of the linked fields is a primary key

  • The related fields are the same data type and size. (If you are using an AutoNumber field, you can relate it to a Number field with a Long Integer Field size.)

  • Both tables are in the same Access database.

  • You can't have a record in a related table unless a matching record already exists in the primary table. Orphan data in a related table is the most common problem people encounter when attempting to establish referential integrity.

Referential Integrity Rules

Once you have established referential integrity, the following rules are set:

  • You can't add a record to a related table unless a matching record already exists in the primary table.

  • You can't change the value of a primary key in the primary table if matching records exist in the related table.

  • You can't delete a record from a primary table if matching records exist in a related table.

How to Enforce Referential Integrity

In this section you will learn how to enforce referential integrity. You should still have the open from the previous lesson in order to complete this lesson.

First, let's establish a relationship between the tblCustomers table and the tblCustomerTours table...

  1. Go to the Database Tools tab. and click on Relationships button to open the Relationships window.
  2. Drag and drop the related tables into the Relationships window.
  3. Drag the CustomerID field from the tblCustomers table to the CustomerID field in the tblCustomerTours table.
  4. The Edit Relationships dialog box appears, as shown in figure. We want to enforce referential integrity so that you can't enter a CustomerID in the tblCustomerTours table unless that CustomerID number already exists in the tblCustomers table.
  5. Check the Enforce Referential Integrity box.
    This will enforce referential integrity between the tables. If you get an error message, it's because your tables and fields don't meet all the required conditions listed on the previous page.
  6. There are two other very important boxes in the Edit Relationships dialog box:
    • Cascade Update Related Fields: When you change data in the main field of one table, Access will automatically update the matching data in the related table.
    • Cascade Delete Related Records: When you delete a record in the main table, Access will automatically delete any matching records in the related table.
    These are both very powerful options think twice before using them.
  7. Check both the Cascade Update Related Fields box and the Cascade Delete Related Records box. Now you can save the changes to the relationship.
  8. Click Create to create the relationship between the tblCustomers and tblCustomerTours tables.
    Access creates the relationship between the two tables and enforces between them. Notice that the join line between the tblCustomers table and the tblCustomerTours table looks a little different. This relationship indicates that referential integrity is being enforced between the two tables and that the tables have a one-to-many relationship, as shown in figure (more about that later).
  9. Click the Close button to close the Relationships window and click Yes to save the changes you made.

In the next section you will get to test the results of your new referential integrity settings.

To Enforce Referential Integrity:

The relationship line between two tables indicates a one-to-many relationship.

  1. Go to the Database Tools tab and click the Relationships button.
    (For Access version prior to 2007, click the Relationships button on the toolbar).
  2. Double-click the join line for the relationship you want to work with.
  3. Check the Enforce Referential Integrity box.
  4. If you want changes to the primary field of the primary table copied to the related field in the related table, check the Cascade Update Related Fields box.
  5. If you want access to automatically delete orphan records in the related table, check the Cascade Delete Related Records box.
  6. Click OK.