How to Understand Referential Integrity in Microsoft Access

Understand that referential integrity is a system of rules that Microsoft Access uses to ensure that relationship data is valid and that you cannot accidentally delete a record in one table if a matching record is present in a related table.

Know that you can enforce referential integrity when you create a relationship, or you can enforce it later by double-clicking on the relationship lines between the tables that you want to use. Click on Enforce Referential Integrity in the window that appears.

Realize that referential integrity cannot be set unless certain conditions are met.

Comprehend that you must observe certain rules once referential integrity is enforced.

Understand that you cannot set referential integrity unless the matching field from the primary table is a primary key or has a unique index.

Know that the related fields must have the same data type. There are two exceptions to this rule.

Realize that both tables must be present in the same Microsoft Access database. If they are linked, they must both be in Access format. You must open the database that contains these tables before you can set referential integrity.

Comprehend that when referential integrity is enforced, you will not be able to enter a value in the foreign key field of the related table unless that value exists in the primary key of the primary table. For example, in your CD database, you cannot assign a CD to an artist that doesn't exist in your Artist table. You could assign a null value to the CD however. This would indicate that the CD is assigned to no one.

Know that when referential integrity is enforced, you will not be able to delete a record from a table if there is a matching record in a related table. For example, you could not delete a record from your CD table if there is a matching record in your Genre table.

Know that you cannot change a primary key value in the primary table if that record has related records. For example, you cannot change the ID of a record in your CD database if the User table shows that this particular ID has been borrowed by a friend.