Turn referential integrity on or off
On the Database Tools tab, in the Relationships group, click Relationships.
On the Design tab, in the Relationships group, click All Relationships.
All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.
Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.
Double-click the relationship line. The Edit Relationships dialog box appears.
Select or clear Enforce Referential Integrity.
Make any additional changes to the relationship, and then click OK.
If you enforce referential integrity, the following rules apply:
You cannot enter a value in the foreign key field of a related table if that value doesn't exist in the primary key field of the primary table — doing so creates orphan records.
You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to that employee in the Orders table. You can, however, choose to delete a primary record and all related records in one operation by selecting the Cascade Delete Related Records check box.
You cannot change a primary key value in the primary table if doing so would create orphan records. For example, you cannot change an order number in the Orders table if there are line items assigned to that order in the Order Details table. You can, however, choose to update a primary record and all related records in one operation by selecting the Cascade Update Related Fields check box.
Notes: If you have difficulty enabling referential integrity, note that the following conditions are required to enforce referential integrity:
The common field from the primary table must be a primary key or have a unique index.
The common fields must have the same data type. The one exception is that an AutoNumber field can be related to a Number field that has a FieldSize property setting of Long Integer.
Both tables exist in the same Access database. Referential integrity cannot be enforced on linked tables. However, if the source tables are in Access format, you can open the database in which they are stored and enable referential integrity in that database.
Set the cascade options
You might encounter a situation where you have a valid need to change the value on the "one" side of a relationship. In such a case, you need Access to automatically update all of the affected rows as part of a single operation. That way the update is completed in full so that your database is not left in an inconsistent state — with some rows updated and some not. Access helps you avoid this problem by supporting the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key.
You might also need to delete a row and all related records — for instance, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and select the Cascade Delete Related Records check box, Access automatically deletes all records that reference the primary key when you delete the record that contains the primary key.
Turn cascade update and/or cascade delete on or off
On the Database Tools tab, in the Relationships group, click Relationships.
On the Design tab, in the Relationships group, click All Relationships.
All tables with relationships are displayed, showing relationship lines. Note that hidden tables (tables for which the Hidden check box in the table's Properties dialog box is selected) and their relationships will not be shown unless Show Hidden Objects is selected in the Navigation Options dialog box.
Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.
Double-click the relationship line.
The Edit Relationships dialog box appears.
Select the Enforce Referential Integrity check box.
Select either the Cascade Update Related Fields or the Cascade Delete Related Records check box, or select both.
Make any additional changes to the relationship, and then click OK