Access 101

Access 101: Referential Integrity

Referential integrity is the key to ensuring data quality in a relational database. Learn what it is and how to enforce it among your back-end tables.

Mike Wolfe

Oct 24, 2023 • 4 min read

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

In the world of relational databases, one of the most critical concepts to grasp is that of referential integrity. It's a fundamental principle that ensures the relationships between tables in a database remain accurate and consistent. It's a bit like the glue that holds your data together, preventing it from falling into a state of disarray.

What is Referential Integrity?

Referential integrity is a set of rules that Access enforces to maintain the consistency and integrity of data across related tables. It ensures that the relationship between two tables remains synchronized during updates and deletes.

In simpler terms, referential integrity is all about keeping your relationships tidy. If you have a record in one table that refers to a record in another table, referential integrity ensures that this reference is always valid.

Why is Referential Integrity Important?

Imagine you're running a library database.

You have a table for Books and a table for Authors. Each book record has a field that identifies its author. Now, suppose you delete an author from the Authors table who still has books in the Books table. Without referential integrity, you would end up with book records pointing to an author that no longer exists. This is known as orphaned data.

Referential integrity protects against this by preventing you from deleting a record from the Authors table if there are related records in the Books table.

How to Implement Referential Integrity in Access

To implement referential integrity in Access, you need to create a relationship between two tables. Here's a step-by-step guide:

Open the Database Tools tab and click on Relationships.
If the tables that you want to relate are not already in the design view, click on Show Table to add them.
Drag the field that you want to relate from one table to the related field in the other table.
In the Edit Relationships dialog box, check the 'Enforce Referential Integrity' box.
Click Create.

If your data is stored in a different backend database, like SQL Server or MySQL, you will need to use the tools and SQL commands in those databases to enforce referential integrity.

You cannot enforce referential integrity in your linked back-end tables from within an Access front-end file.

Cascade Updates and Deletes

When defining a relationship in Access, you have the option to enable 'Cascade Update Related Fields' and 'Cascade Delete Related Records'.

'Cascade Update Related Fields' ensures that if you change the primary key of a record, all related records will be updated to reflect this change.

'Cascade Delete Related Records' will delete all records that are related to a record when you delete that record. This can be useful in some situations, but be careful. It's a powerful option that can result in large amounts of data being deleted at once.

I never use Cascading Updates or Cascading Deletes.

You can completely avoid the need for cascading updates by always using Autonumber fields as the primary keys for your tables. Cascading updates only make sense when you use a "natural" key, such as a person's name. If you use a "surrogate" key–a value whose only purpose is to uniquely identify a record–then you can safely change any other value in the table without having to worry about breaking existing relationships.

As for cascading deletes, I consider their potential for doing widespread damage to be too great. Instead, I prefer to either:

Force the user to remove related records before deleting the parent record; OR
Explicitly delete the related records in code or via a DELETE query if I'm sure that's what I want to do

Conclusion

Referential integrity is a powerful tool in your Access toolkit.

It helps to maintain the consistency and accuracy of your data, making your databases more reliable and easier to manage. While it can seem complex at first, once you understand the principles behind it, it becomes an invaluable part of your database design process. Be sure to take the time to understand what it is and how it works.

If you don't enforce referential integrity among your back-end tables, you cannot guarantee the quality of your data.