Database - Planning your Access Database
Enforcing Referential Integrity
Before you can enforce referential integrity in a relationship, three conditions must be met.
At least one of the related fields must be a primary key or have a unique index. A bold item in a table list indicates a primary key.
The related fields must be the same data type. There is one exception: You can relate a Number and an AutoNumber field.
The related tables must be in the same database. You can't enforce referential integrity between linked tables.
Access doesn't enforce referential integrity by default; you must turn it on when you relate two tables. Let's look at the relationships between Northwind's Categories and Products tables. Right-click on the join line between the two tables and choose Edit Relationship.
The two table controls display the current relationship between the CategoryID fields in both tables, as shown in Figure 2. The Enforce Referential Integrity option in the Edit Relationships dialog is checked. This means that Access will accept foreign key values in the child table (Products) only when a matching primary key value exists in the parent table (Categories), and it won't let you delete a primary key value in the parent table if the child table contains a matching foreign key value.
To see how these rules protect your data, click on the Cancel button to close the Edit Relationships dialog, then press F11 to display the Database Window. Open the Categories table, select any record, and select Edit | Delete Record. When you do, Access displays an error message stating that the Products table contains related records, so it won't delete the Categories record. Clear the message by clicking on OK.
You would also get an error if you attempted to add a record to the Products table without specifying a valid category. Access won't let you add a foreign key value if there's no matching primary key value in the related parent table. The key value Category is a lookup field, however, so you have to select an existing category.