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