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.

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• Microsoft Access

Relationship Integrity

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.
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.
3. Realize that referential integrity cannot be set unless certain conditions are met.
4. Comprehend that you must observe certain rules once referential integrity is enforced.

Required Conditions for Setting Referential Integrity

5. Understand that you cannot set referential integrity unless the matching field from the primary table is a primary key or has a unique index.
6. Know that the related fields must have the same data type. There are two exceptions to this rule.
7. 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

8. 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.
9. 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.
10. 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.

Tips & Warnings

• Setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes allows you to override restrictions against changing data and still preserve referential integrity.
• The primary table is the table that is the "one" side of a one-to-many relationship.
• A unique index will not allow duplicate records.

Who Can Help:

• Find Computer, Electrical and Telephone Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.
• Find Electrical Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.