Part I: Access Building Blocks
124
Note
You might find, when you select Enforce Referential Integrity and click the Create button (or the OK button if
you’ve reopened the Edit Relationships window to edit a relationship), that Access will not allow you to create
a relationship and enforce referential integrity. The most likely reason for this behavior is that you’re asking
Access to create a relationship that violates referential integrity rules, such as a child table with orphans in it.
In such a case, Access warns you by displaying a dialog box similar to that shown in Figure 3.18. The warning
happens in this example because there are some records in the Sales table with no matching value in the
Salesperson table. This means that Access can’t enforce referential integrity between these tables because
the data within the tables already violates the rules.
FIGURE 3.18A dialog box warning that referential integrity cannot be enforced because of integrity violations
Tip
To solve any conflicts between existing tables, you can create a Find Unmatched query by using the Query
Wizard to find the records in the many-side table that violate referential integrity. Then you can convert the
Unmatched query to a Delete query to delete the offending records or add the appropriate value to the
SalespersonID field.
You could remove the offending records and return to the Relationships window and set referential
integrity between the two tables. Whether it’s appropriate to clean up data by deleting records
depends entirely on the business rues governing the application. Deleting orders just because refer-
ential integrity can’t be enforced would be considered a bad idea in most environments.Choosing the Cascade Update Related Fields option
If you specify Enforce Referential Integrity in the Edit Relationships dialog box, Access enables the
Cascade Update Related Fields check box. This option tells Access that, as a user changes the con-
tents of a related field (the primary key field in the primary table — CustomerID, for example),
the new CustomerID is rippled through all related tables.Note
If the primary key field in the primary table is a related field between several tables, this option must be
selected for all related tables or it won’t work.