Enforcing Referential Integrity | 10 Minute Guide to Microsoft Access 2002


Team-Fly

	10 Minute Guide to Microsoft Access 2002 By Joe Habraken
	Table of Contents

	Lesson 11. Creating Relationships Between Tables

Enforcing Referential Integrity

In the Edit Relationships box is a check box called Enforce Referential Integrity. What does this mean? Referential integrity means that data entered in a field that is used to link two tables must match from one table to another. Actually, the data entered in the table where the field does not serve as the primary key must match the entries that are in the table where the field serves as the primary key.

Plain English

Referential Integrity The data contained in a primary key field used in a table relationship must be matched in that same field in the secondary table. Otherwise, Access returns an error message.

For example, you could link a Customers table that has a Customer ID field as its primary key to an Orders table that also holds the Customer ID field, where it does not serve as the primary key (the Customer ID is providing the link for the relationship). If you enforce referential integrity, values entered into the Order table's Customer ID field must match values already entered into the Customers table's Customer ID field. Enforcing referential integrity is a way to make sure that data is entered correctly into the secondary table.

When referential integrity is breached during data entry, (meaning a value is entered into the secondary table in the relationship that was not in the linking field of the primary table), an error message appears (see Figure 11.5). This error message lets you know that the field value you have entered in the linking field is not contained in a record in the other table in the relationship (where the field is the primary key).

Figure 11.5. Enforcing referential integrity means that values entered in the linking field must be contained in the field in the table where it serves as the primary key.

Two other options are possible when data entered into a field violates referential integrity. Figure 11.6 shows the Edit Relationships with the Enforce Referential Integrity box selected. The two additional options provided are

Cascade Update Related Fields?/b> If this check box is selected, any data changes that you make to the linking field in the primary table (Customers, in this example) are updated to the secondary table. For example, if you had a customer in the Customers table listed with Customer ID 5 and you changed that to Customer ID 6, any references to Customer ID 5 would be updated to Customer ID 6 in the Orders table.
Cascade Delete Related Fields?/b> If this check box is marked and you change the linking field's data in the primary table so that it no longer matches in the secondary table, the field information is deleted from the secondary table. Therefore, if you changed a Customer ID number in the Customers table, the field data in the Customer ID field in the Orders table would be deleted.

Figure 11.6. The Edit Relationships dialog box is used to change the options related to a particular relationship.

You should probably set up your relationships and enforce referential integrity before you do any data entry in the related tables. You should also typically enter the data first into the table where the linking field is the primary key. For example, you should fill in as much of your Customers table information as possible before you try to fill the data fields in the related Orders table.


Team-Fly

Top