Lesson · 22 min

Relationships and Referential Integrity

Link tables so Access enforces the connections between them, and understand what referential integrity actually prevents.

By the end of this lesson you can

Create a relationship between two tables
Explain one-to-many and many-to-many relationships
Enable referential integrity and describe what it enforces
Explain what cascade update and cascade delete do

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

Building the relationship

Worked example

Linking Appointments to Patients

Connect the two tables so Access refuses to accept an appointment for a patient who does not exist.

1
Open Database Tools > Relationships and add both tables.
The Relationships window is where links are defined for the whole database, not per-query. A join drawn inside one query applies only to that query; a relationship defined here is enforced everywhere, including direct table entry.
2
Drag PatientID from Patients onto PatientID in Appointments.
Drag from the 'one' side to the 'many' side — from the primary key to the foreign key. Access reads the direction to decide which table is which, and dragging the wrong way produces a relationship that does not mean what you intended.
3
Tick Enforce Referential Integrity before clicking Create.
This is the whole point of the exercise, and it is off by default. Without it you get a line on a diagram and no enforcement — Access will happily accept an appointment for PatientID 4471 when no such patient exists.
4
Confirm Access reports the relationship as One-To-Many.
If it says One-To-One, the foreign key has a unique index on it and each patient could have only one appointment. If it says Indeterminate, the fields are different data types — usually a Number joined to a Short Text — and integrity cannot be enforced until you fix that.
5
Test it: try to enter an appointment with a PatientID that does not exist.
A relationship you have not tested is a relationship you are assuming. Access should refuse the record. If it accepts it, integrity is not actually on.

Result: Appointments can only reference real patients, and a patient with appointments on file cannot be deleted.

Drag from the one side to the many side, and tick Enforce Referential Integrity — it is off by default, and without it the relationship is decorative.

Relationships turn tables into a database

On the job

You finish the appointment database at Lakeside Medical Associates.

Your tables are well designed but unconnected. Nothing stops someone entering an appointment for PatientID 4471 when no such patient exists, and nothing warns anyone deleting a patient who still has twelve appointments on file. Those appointments become orphans pointing at nobody.

Your task: Define relationships so Access enforces the links your design assumes.

A relationship connects a primary key in one table to a foreign key in another. The overwhelmingly common form is one-to-many: one patient has many appointments; one provider has many appointments. The 'one' side holds the primary key and the 'many' side holds the foreign key.

Type	Means	Example
One-to-many	One record relates to many in the other table	One patient, many appointments
One-to-one	One record relates to exactly one	A patient and a rarely-used detail record
Many-to-many	Records on both sides relate to many	Patients and insurance plans — requires a junction table

Relationship types

Access cannot represent many-to-many directly. You create a third junction table holding a foreign key to each side, which turns one many-to-many into two one-to-many relationships. A PatientInsurance table linking PatientID to PlanID is the standard pattern.

Referential integrity is the rule Access enforces once you switch it on. It refuses to let you create an appointment for a patient who does not exist, and refuses to delete a patient who still has appointments. It converts your design assumption into something the database actually guarantees.

Cascade Delete Related Records removes the children when you delete the parent. Deleting one patient silently deletes all twelve of their appointments, with no second confirmation. That is occasionally what you want and usually a disaster in a records system where history must be retained. Leave it off unless you have a specific reason, and think hard before enabling it on anything holding patient data. Cascade Update, which propagates a changed key value, is far safer and rarely needed if your keys are AutoNumbers that never change.

Check your understanding

With referential integrity enabled and cascade delete off, what happens when you try to delete a patient who has twelve appointments on file?

Challenge

Apply what you've learned in this lesson.

Build the relationships your design implies, then test that they hold.

Create Patients, Providers, and Appointments tables from your Module 1 design, with appropriate keys.
Open Database Tools > Relationships and create one-to-many relationships from Patients and Providers to Appointments. Enable referential integrity on both.
Try to enter an appointment with a PatientID that does not exist. Record what happens.
Try to delete a patient who has appointments. Record what happens, then explain in two sentences why you would not enable cascade delete on this database.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

← Creating Tables and Choosing Data Types Module 3: Queries →