How to Understand Referential Integrity in Microsoft Access

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

Relationship Integrity

Video of the Day

Step 1

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.

Advertisement

Video of the Day

Step 2

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 3

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

Advertisement

Step 4

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

Required Conditions for Setting Referential Integrity

Step 1

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

Advertisement

Step 2

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

Step 3

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.

Advertisement

Advertisement

Rules to Follow When Referential Integrity Is Enforced

Step 1

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.

Advertisement

Step 2

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 3

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.

Advertisement

Advertisement