Advanced Relationships in Access: Referential Integrity and Cascades

Most Access users know how to draw a line between two tables in the Relationships window. But the real power of Access relationships comes from referential integrity, cascade options, and the patterns for handling many-to-many and self-referencing relationships. These features are what separate a well-designed database from a fragile one.

Referential Integrity: The Guardian of Your Data

Referential integrity (RI) is a set of rules that prevents orphaned records — child records that reference a parent that does not exist.

Without RI, you can delete a customer who still has orders. The orders remain, but their CustomerID points to nothing. Queries that join Customers to Orders will silently drop those orders from results. Reports will be wrong. Data will be lost.

With RI enforced, Access prevents:

• Adding a child record with a foreign key that does not exist in the parent table
• Deleting a parent record that has related child records
• Changing a parent's primary key if child records reference it

To enforce referential integrity:

1. Open the Relationships window (Database Tools → Relationships)
2. Double-click the relationship line between two tables
3. Check Enforce Referential Integrity
4. Click OK

The relationship line changes to show "1" on the one side and "∞" on the many side, confirming RI is active.

Cascade Update Related Fields

When RI is enforced, you cannot change a primary key value if child records reference it. But sometimes you need to change a key — for example, correcting a product code.

Cascade Update allows the primary key to be changed, and automatically updates all matching foreign key values in related tables.

To enable: In the Edit Relationships dialog, check Cascade Update Related Fields.

Now if you change ProductCode "WGT-A" to "WGT-001" in the Products table, Access automatically updates every OrderDetail record that referenced "WGT-A" to "WGT-001".

Use cascade update when:

• Your primary key is a meaningful code (not an AutoNumber) that might need correction
• You have a controlled vocabulary table (categories, statuses) where values might be renamed

Cascade Delete Related Records

Cascade Delete allows a parent record to be deleted, and automatically deletes all related child records.

To enable: In the Edit Relationships dialog, check Cascade Delete Related Records.

Use cascade delete with extreme caution. Deleting a customer will also delete all their orders, all order details, all invoices — the entire chain of related records. This is appropriate for some scenarios (deleting a test record and all its related data) but catastrophic for others (accidentally deleting a real customer).

Best practice: Enable cascade delete only when the child records have no independent meaning without the parent. For most business data, it is safer to prevent deletion of parents with children and handle cleanup manually or through a controlled archiving process.

Many-to-Many Relationships

A many-to-many relationship cannot be represented directly in a relational database — you need a junction table (also called a bridge table or associative table) in between.

Example: Students and Courses. A student can enroll in many courses; a course can have many students.

Junction table: Enrollments

EnrollmentID	StudentID	CourseID	EnrollDate	Grade
1	42	101	9/1/2026	A
2	42	105	9/1/2026	B+
3	43	101	9/1/2026	A-

The Enrollments table has:

• A foreign key to Students (StudentID)
• A foreign key to Courses (CourseID)
• Its own primary key (EnrollmentID, or a composite key of StudentID + CourseID)
• Any additional attributes of the relationship (EnrollDate, Grade)

In the Relationships window, this creates two one-to-many relationships:

• Students → Enrollments (one student, many enrollments)
• Courses → Enrollments (one course, many enrollments)

Self-Referencing Relationships

A self-referencing (or recursive) relationship is where a table has a foreign key that points back to itself. The classic example is an employee hierarchy:

Employees table:

EmployeeID	Name	ManagerID
1	Alice (CEO)	NULL
2	Bob	1
3	Carol	1
4	Dave	2

ManagerID is a foreign key that references EmployeeID in the same table. Alice has no manager (NULL); Bob and Carol report to Alice; Dave reports to Bob.

In the Relationships window, add the Employees table twice (it appears as "Employees" and "Employees_1") and draw a relationship from EmployeeID to ManagerID.

Querying a self-referencing table:

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees AS e
LEFT JOIN Employees AS m ON e.ManagerID = m.EmployeeID;

The LEFT JOIN ensures employees with no manager (like the CEO) still appear in the results.

Viewing and Printing the Relationships Diagram

The Relationships window is your visual documentation of the database structure. To print it:

1. Open the Relationships window
2. Go to File → Print → Print Relationships
3. Access generates a report showing the relationships diagram

This report is invaluable for documentation and for onboarding new developers to an existing database.

Conclusion

Referential integrity is not optional in a production database — it is the enforcement mechanism that keeps your data consistent. Enable it on every relationship, think carefully about cascade options, and design junction tables for every many-to-many relationship. These practices are what separate databases that stay reliable for years from ones that accumulate data anomalies and require constant cleanup.