How to Understand Referential Integrity in Microsoft Access

How to Understand Referential Integrity in Microsoft Access. You can ensure that relationships between records in related tables are valid by enforcing referential integrity in Microsoft Access. For example, if referential integrity is enforced, you will not be allowed to delete a record from your Customer table if there is a matching record in your Sales table. These instructions are for Access 97.

Relationship Integrity

Step

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.

Step

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.

Step

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

Step

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

Required Conditions for Setting Referential Integrity

Step

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

Step

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

Step

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.

Rules to Follow When Referential Integrity Is Enforced

Step

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.

Step

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.

Step

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.