Enforce Referential Integrity

When you create a relationship between two tables, it is usually a good idea to enforce referential integrity. Referential integrity keeps data accurate and ensures that you don’t accidentally change or delete related data in one table but not in the other.

Conditions for Enforcing Referential Integrity

Access is very picky about when you can set referential integrity. You can only use referential integrity when all the following conditions are met:

• One of the linked fields must be a primary key.
• The related fields must be the same data type and size.
• Both tables must be in the same Access database.
• You can't have a record in a related table unless a matching record already exists in the primary table.

Once You Have Established Referential Integrity

Once you have established referential integrity, the following rules are set:

• You can’t add a record to a related table unless a matching record already exists in the primary table.
• You can’t change the value of a primary key in the primary table if matching records exist in the related table.
• You can’t delete a record from a primary table if matching records exist in a related table.

Enforce Referential Integrity

1. Click the Database Tools tab.
2. Click the Relationships button.
   

   Here you can specify the tables you want to be linked together.

3. Click the Show Table button and add tables to the Relationships window.
4. Click the related field in the first table and drag it to the related field in the second table.
   
5. Check the Enforce Referential Integrity option.
6. Check the Cascade Update Related Fields box.

   This ensures that any changes you make in one linked field are automatically updated in the corresponding field.

7. Check the Cascade Delete Related Records box.

   This ensures that when you delete a record in the main table, Access will automatically delete any matching records in the related table.

8. Click Create.
   

Access creates the relationship between the two tables and enforces referential integrity between them.

The join line between the tables looks different than normal. This relationship indicates that referential integrity is being enforced between the two tables and that the tables have a one-to-many relationship.