Referential Integrity in MS Access

Referential integrity maintains valid relationships between records in different tables. It helps prevent records from referring to parent records that do not exist.

Example: Courses and Students

Courses Table

Students Table

Every CourseID value in the Students table refers to a CourseID that exists in the Courses table.

• Maintains consistency between related tables.
• Prevents invalid foreign-key references.
• Helps protect related data from accidental changes.
• Improves the reliability of queries and reports.
• Reduces the risk of orphan records.

What Is an Orphan Record?

An orphan record is a child record that refers to a parent record that does not exist.

For example, a student record containing CourseID 99 is an orphan record if CourseID 99 does not exist in the Courses table and the relationship requires a matching course.

Primary Key

A primary key uniquely identifies every record in a table. It cannot contain duplicate values or Null.

Courses
-------------------------
CourseID     Primary Key
CourseName

Foreign Key

A foreign key stores a value that refers to a key in another table.

Students
-------------------------
StudentID    Primary Key
StudentName
CourseID     Foreign Key

In this example, Students.CourseID refers to Courses.CourseID.

Referential integrity is applied to a relationship between tables. You must first create the relationship using the appropriate fields.

Steps

1. Open your Microsoft Access database.
2. Ensure that both related tables exist.
3. Verify the primary key in the parent table.
4. Verify the matching foreign key in the child table.
5. Open the Database Tools tab.
6. Click Relationships.
7. Add the required tables to the Relationships window.
8. Drag the parent table's key onto the matching field in the child table.
9. Review the Edit Relationships dialog box.

You can enable referential integrity in the Edit Relationships dialog box if the fields and existing records meet the requirements.

The Enforce Referential Integrity option tells Access to maintain the required relationship rules between the related tables.

Steps to Enable Referential Integrity

1. Open the Database Tools tab.
2. Click Relationships.
3. Locate the relationship line between the tables.
4. Double-click the relationship line.
5. In the Edit Relationships dialog box, select Enforce Referential Integrity.
6. Review the available cascade options.
7. Click OK.
8. Save the relationship layout.

Suppose the Courses table contains CourseID values 1, 2, and 3. Referential integrity is enforced between Courses and Students.

Valid Student Record

StudentID:   101
StudentName: Rahul Kumar
CourseID:    1

This record can be valid because CourseID 1 exists in the Courses table.

Invalid Student Record

StudentID:   104
StudentName: Mohan Kumar
CourseID:    99

This record cannot be saved with CourseID 99 when that course does not exist and referential integrity requires a matching course.

A parent record is a record in the table that contains the referenced key. A child record contains the corresponding foreign key.

For example, Courses is the parent table and Students is the child table in our example.

When Referential Integrity Is Enforced

If a course has related student records, Access normally prevents deleting that course while those related records remain, unless an applicable cascade-delete option is enabled.

Example

CourseID 1 is used by Rahul and Amit. Deleting the ADCA course could leave their records referring to a nonexistent course.

Referential integrity protects against this situation unless the relationship is configured to handle the deletion through cascading.

Cascade Update Related Fields is an option available for supported relationships. It propagates changes to a referenced key to matching foreign-key values.

Example

Suppose CourseID 10 is referenced by several student records. If the referenced key changes and Cascade Update is enabled, Access can update the related foreign-key values accordingly.

Steps

1. Open the Relationships window.
2. Double-click the relationship line.
3. Check Enforce Referential Integrity if it is not already selected.
4. Select Cascade Update Related Fields if appropriate.
5. Click OK and save the relationship layout.

Cascade Delete Related Records allows Access to delete related child records when their parent record is deleted.

Example

If CourseID 1 is used by several students and Cascade Delete is enabled, deleting that course can also delete the related student records.

Steps

1. Open the Relationships window.
2. Double-click the relationship line.
3. Ensure Enforce Referential Integrity is selected.
4. Select Cascade Delete Related Records if required.
5. Click OK and save the relationship layout.

Problem 1: Relationship Cannot Be Created

Solution: Check whether the related fields have compatible data types and whether the parent field is a suitable key.

Problem 2: Referential Integrity Cannot Be Enabled

Solution: Check for unmatched foreign keys in the child table. Every non-null foreign-key value subject to the relationship must match a valid parent key.

Problem 3: A Record Cannot Be Deleted

Solution: Check whether child records reference the parent record. Decide whether to keep those records, update their references, or use a carefully considered cascade-delete rule.

Problem 4: Data Types Do Not Match

Solution: Review the field definitions in both tables. For example, an AutoNumber key commonly uses a Number field with Long Integer size as its related foreign key.

Problem 5: Existing Data Contains Invalid References

Solution: Identify the invalid foreign keys and correct them by adding the appropriate parent records or changing the foreign-key values.

Create a Students and Courses database and enforce referential integrity between the tables.

Courses Table

• CourseID — AutoNumber, Primary Key
• CourseName — Short Text

Students Table

• StudentID — AutoNumber, Primary Key
• StudentName — Short Text
• CourseID — Number, Long Integer

Tasks

1. Create both tables.
2. Add at least three courses.
3. Add student records with valid CourseID values.
4. Open Database Tools and select Relationships.
5. Connect Courses.CourseID to Students.CourseID.
6. Enable Enforce Referential Integrity.
7. Save the relationship.
8. Try inserting a student with a nonexistent CourseID.
9. Observe the result.
10. Review the cascade options without enabling deletion unless needed.

1. What is referential integrity?
2. What is the difference between a primary key and a foreign key?
3. What is an orphan record?
4. Where can you enable referential integrity in Access?
5. What happens when you insert an invalid foreign-key value?
6. Why can Access prevent the deletion of a parent record?
7. What does Cascade Update Related Fields do?
8. What does Cascade Delete Related Records do?
9. Why should cascade deletion be used carefully?
10. What should you check if referential integrity cannot be enabled?