What does referential integrity mean in Microsoft Access? This article describes the two key rules of referential integrity and summarises how this feature is applied to table relationships in an Access database.

Access databases are relational, such that tables of data can be related to one another through linked fields. Each Access tables normally has a primary key which uniquely identifies every record in the table, so the primary key does not allow duplicate values. All other fields in the table can allow duplicate values. Suppose for example you have a table of staff names with the primary key as StaffID. Each person has a separate StaffID and each one is different. We also have a second table listing Sales, with its own primary key SalesID which uniquely identifies each sale. The table also has a StaffID field to indicate which member of staff made the sale. So each table has its own primary key, and the sales table also has a StaffID field to show who made the sale.

In Access you use the Relationships view to create the links between tables. The most popular relationship links a primary key field in one table to a non primary key field in another table, creating a one to many relationship. Fields linked in this way must be the same data type. So with our example database with two tables, we can link StaffID primary key from the staff table to the StaffID non primary key field in the Sales table. In the Relationships panel you can also tick a checkbox for "Enforce Referential Integrity". If you tick this box then OK to finish, you'll see the relationship drawn as a line between the two tables. Because you enforced referential integrity, there is also an arrowhead on the line pointing to the Sales table. If you don't enforce referential integrity, the link will still be made but without the arrowhead.

Enforcing Referential Integrity means that a record must exist in the staff table before that member of staff can make a sale in the Sales table. So if you try to add a sales record in the Sales table and use a StaffID not already in the Staff table, Access will block the data entry with an error prompt explaining that the new record breaks the rules of referential integrity. If you have data already in both tables before you enforce referential integrity, the data must be consistent with this rule if you then enforce it. So StaffIDs in the Sales table must also be in the Staff table before you can apply referential integrity. If this is not the case, Access will refuse to apply Referential Integrity.

So Referential Integrity means that you cannot have a member of staff making sales if they are not already in the Staff table. By the same token, if you try to delete a staff record in the staff table and that person already has some sales in the Sales table, Access will block the deletion because this would break the second rule of Referential Integrity. You cannot delete a record in the one side of the relationship if that same record has entries in the many side of the relationship.

Enabling Referential Integrity protects your data from inconsistencies with these two very powerful rules. Firstly data must be in the table on the one side of the relationship before it can be used in the table in the many side of the relationship. Secondly you cannot delete a record on the one side if it already occurs on the many side.

There are two further features which can also be used with Reverential Integrity. These are Cascade Update, and Cascade Deletions. If you apply Cascade Updates, and you then change a primary key value in the table on the one side of the relationship, then all entries in the corresponding field in the table on the many side of the relationship are changed. So for example if you change the StaffID of one member of staff to a new value, and that many of staff already has entries in the Sales table, then all these entries are updated. This ensures the same sales records are correctly associated with the same member of staff. However if you've chosen an autonumber field for the StaffID in the one table, you cannot change an autonumber value and this feature will have no effect.

If you intend applying Cascade Deletions, consider backing up your database first, or at least copy the relevant tables, because the action is not reversible. If you apply Cascade Updates, and you then decide to delete a member of staff from the Staff table, the referential integrity rules does not apply and record WILL be deleted, AND all related records in the table on the many side will also be deleted. So if you delete Mary Smith from the Staff table, for example, all the sales records for Mary Smith will be deleted from the Sales table. Access will prompt you to indicate how many records are about to be deleted, but if you proceed, the action is not reversible, so apply with great caution.

If you are using these rules for the first time, you might consider applying Referential Integrity with your table relationships without selecting Cascade Update or Cascade Deletions. This will ensure that data must be entered into the table on the one side of the relationships before it is added to the table on the many. Consider using the other two options with care as their actions are not reversible.

Interested in learning more about Access? A really effective way is to attend a training course. You can learn a great deal in a short time, and really watch your Access skills move forward.