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.
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
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.