What is referential integrity?

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

• The matching field from the primary table is a primary key or has a unique index.
  
  
• The related fields have the same data type. There are two exceptions. An AutoNumber field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
  
  
• Both tables belong to the same Microsoft Access database. If the tables are linked tables, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.

When referential integrity is enforced, you must observe the following rules:

• You can't enter a value in the foreign key field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
  
  
• You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
  
  
• You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.

If you want Microsoft Access to enforce these rules for a relationship, select the Enforce Referential Integrity check box when you create the relationship. If referential integrity is enforced and you break one of the rules with related tables, Microsoft Access displays a message and doesn't allow the change.

You can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes. When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records. When the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table. For more information on Cascade Delete Related Records and Cascade Update Related Fields check boxes, click .

For information on how to edit an existing relationship and set referential integrity, click .

For information on how to define a new relationship, click .