22 min read
Building Relationships and Enforcing Referential Integrity
Database Tools → Relationships: drag a primary key onto a matching foreign key, and check Enforce Referential Integrity so Access actively prevents orphaned records.
You will learn: how to open the Relationships window. how to draw lines between matching fields. You will do: follow the 12-step click path. create three relationship lines.
12-Step Click Path
Goal
Draw the three library relationships in the Relationships window.
Lines tell Access which fields must match. You draw them once and save the layout.
Open the database
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Three lines appear between the four tables.
- The crow's-foot (∞) sits on Loans and Fines — the many side.
- One end connects to Members, Books, and Loans primary keys.
If something looks wrong
Fix
Database Tools tab
Relationships, compact, and other file tools.
Target layout
The bar marks the ONE side (the primary key). The three lines mark the MANY side (the foreign key). The line joins those two fields — it is not a flow of data.
Click a key icon, a field name, or a relationship line to highlight the matching fields.
What each line means
| Line | Meaning |
|---|---|
| Members.MemberID ↔ Loans.MemberID | Every loan must point at a real member |
| Books.BookID ↔ Loans.BookID | Every loan must point at a real book |
| Loans.LoanID ↔ Fines.LoanID | Every fine must point at a real loan |
Practice
Swipe or use the arrows to move between questions.
1 / 3
You will learn: what referential integrity does. how to turn it on in Edit Relationship. You will do: tick Enforce Referential Integrity. test that MemberID 99 is refused.
A relationship line shows which fields match. Referential integrity adds a rule: every foreign key must point at a real primary key.
Without the rule, RiversideLibrary.accdb can store a loan with MemberID 99 when no member 99 exists — an orphan row.
Enforce Referential Integrity
Goal
Turn on Enforce Referential Integrity on each relationship.
Access refuses orphan loans and dangling fines at save time.
Open Relationships
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Each line looks thicker — 1 on the one side, ∞ on the many side.
- Access will block bad foreign keys.
If something looks wrong
Fix
Cascade Options — Leave Off
Two optional cascade checkboxes
| Cascade Update Related Fields | Cascade Delete Related Records |
|---|---|
| If Members.MemberID 1 changes to 10, Access updates Loans.MemberID from 1 to 10 | If you delete a member, Access deletes every loan row for that member too |
| Rarely needed when keys are AutoNumber and never edited | Dangerous — deleting a member would erase loan history |
| Leave off for this course | Leave off — return books first, or keep the member row |
Test — MemberID 99 Refused
| Members | |||
|---|---|---|---|
Goal
Try to save a loan with MemberID 99.
Confirms that Enforce Referential Integrity is working.
Confirm integrity is on
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Access refuses the row.
- An error explains that the relationship must be satisfied — no member 99.
If something looks wrong
Fix
Delete a Member Who Still Has Loans
Goal
Try to delete Sophie Walsh (MemberID 1) while loan 102 still stores MemberID 1.
Shows delete protection with integrity on and cascade delete off.
Confirm open loan exists
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Access refuses the delete.
- The related loan must be dealt with first — return the book or keep the member row.
If something looks wrong
Fix
Practice
Swipe or use the arrows to move between questions.
1 / 3
Field Notes: Building the Link
Goal
Create a relationship between two tables and enforce referential integrity.
A relationship teaches Access which fields connect two tables, and referential integrity actively protects that connection from broken links.
Open the Relationships window
Do this
Verification
Check your work
You are correct if all of these are true:
- A line now connects the two tables in the Relationships window, showing a "1" near the parent table and an infinity symbol near the child table for a one-to-many relationship.
Checking Enforce Referential Integrity tells Access to actively prevent "orphaned" records — for example, it will refuse to let you create an Order for a CustomerID that doesn't actually exist in the Customers table, and by default it will also refuse to let you delete a customer who still has existing orders.
More lessons in Microsoft Access · Next: Normalization: Designing a Database That Won't Break · Previous: Understanding Relationships: One-to-Many and More