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. This means that the table containing the primary key dictates what data can go into the foreign key field in the other table. If you don't have the data in the primary key field, it can't be entered in the foreign key field because it can't be referenced (and its integrity is in doubt).

Referential Integrity When you enter data into the foreign key field used in the table relationship it must match data that is already contained in the primary key field in the other table. If the data differs , 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 9.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 9.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 9.6 shows the Edit Relationships dialog box with the Enforce Referential Integrity box selected. The two additional options provided are

• Cascade Update Related Fields 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 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 9.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.

Be advised, however, that you do not have to enforce referential integrity for the tables to function correctly. The only risk that you run is that you can enter incorrect data into the foreign key field in the secondary table that does not match data in the primary key field in the primary table. This makes a mess out of forms, queries, and reports that you run taking advantage of the relationship between the tables (meaning Access won't be sure what to do with the incorrect data).