Microsoft Access Tips: Should You Enforce Referential Integrity?

Referential Integrity enables you to maintain a consistent and validated database. For example, in a patient database you may want to ensure that no patients can be entered into the system without a unique patient ID. Otherwise, you could end up with an inability to quickly index, sort and find patients who have corresponding episodes of care, appointments, historical cases, etc.

Equally, you may want to ensure that patients with open episodes of care—such as those that are inpatients or are on a waiting list—cannot be deleted or amended, because such data changes could have a dangerous effect on your database's integrity.

Whether the problem causes lost or corrupt patient records, either way such data could conceivably be left unattached, floating around your system with no placeholder or logical path. Thus, without referential integrity you will be left with a database that cannot run at an optimum performance level and is at risk from data loss.

Referential integrity rules prevent you from:

a) Adding records to a foreign key in the related table where there are no matching values in the primary key of the parent table. For example, in the scenario discussed above, a patient must have a corresponding casenote number for every episode of care.

b) Changing values in the primary key field of the parent table if there are matching records in any related tables. For example, you cannot change the name of a patient if there are historical or current episodes of care for the patient.

c) Deleting records from the primary table when there are matching records in any related tables. For example, you cannot delete a patient from the Master Patient Index table if there are any historical or current episodes of care for the patient.

In terms of changing or deleting records, referential integrity can be overridden by selecting the cascade update or cascade delete option. However this is not advisable, certainly not for the novice database creator, and it doesn't adhere to good relational database build standards.

It is always a good idea to set referential integrity prior to populating tables with data. But before you define the referential integrity rules for each relational table, please ensure;

• that the matching field in the primary table is a primary key or has a unique index
• that related fields have the same data types (with the exception of AutoNumber which can match Number)
• that all tables are in the same database and have linked relationships

1. To set referential integrity, in the dialog which allows you to define relationships between tables, click the Enforce Referential Integrity box.

2. Click OK and Microsoft Access will work out the type of relationship and its corresponding referential integrity status, i.e. either one-to-one or one-to-many.

Setting relationships and enforcing referential integrity is a very important part of creating a quality database structure. As such, it is best to create the tables with their relationships and referential integrity as early as possible, thus avoiding problems trying to set up a relationship when data already exists within tables, which in turn can cause conflicts with referential integrity rules. As with everything in database design, a blank canvas is as important during table configuration as it is in any other phase of database design.

However, it isn't always possible to do this and databases will need administering on a regular basis, thus referential integrity may need to be set at a later date. Therefore;

1. In the database window click the Relationships button.

2. Right click on the join line to edit or delete the relationship.

In the Relationships dialog box, you will also see the presence of two options to Cascade: Update and Delete. For relationships where referential integrity is enforced you may want to specify when you want Access to automatically cascade updates and cascade delete related records. When these options are ticked the update and delete option of operation that would in normal circumstances be prevented by referential integrity rules, would be allowed. In such instances, when you update or delete a record—thus changing the primary key values in the primary table—Access will make the necessary changes to all related tables to preserve referential integrity.

To be clear!

By selecting the Cascade Update Related Fields checkbox, anytime you change the primary key of a record, Access automatically updates the primary key to the new value in all related records.

Select Cascade Delete Related Records to automatically delete related records in all related tables.

These are very serious changes you will be making to the entire database—the domino effects can be very widespread indeed. For example, if you were to have these settings in place, for every patient record that were to be deleted, all corresponding episodes of care, both historic and current would be wiped out!

More Microsoft Access Database Tutorials

In our next Access tutorial, we will discuss Join Properties and Join Types. Find out more about how Inner and Outer joins enable database queries to run expediently and produce the output reports and information you need.

Microsoft Access Official Site, http://office.microsoft.com/en-us/access-help/

Image: Wikimedia Commons/Everaldo Coelho and YellowIcon