28 min read
Orphan Records and Referential Integrity
George creates a loan for MemberID 99 and BookID 40. Sophie cannot be deleted while she still has a loan.
George Clarke is still at the desk. A visitor is in a hurry. George cannot find them in Members, so he invents a number. He types a new loan:
LoanID 105. MemberID 99. BookID 40. LoanDate 15 May 2026.
There is no member 99. There is no book 40. The loan points at nobody and at nothing.
Amelia Hart searches for 99 and finds an empty result. "Who is this loan for?"
Oliver Bennett is clearer. "That loan is an orphan. It belongs to nobody. If we also deleted Sophie Walsh of York while she still has loan 102, we would make another orphan on purpose."
| Loans — one row points at nobody | ||||
|---|---|---|---|---|
| Members | |||
|---|---|---|---|
| Books | ||
|---|---|---|
Loan 105 is a row whose pointers have nothing to point at. The MemberID is a number. The BookID is a number. Matching nobody is still a failure.
Orphans appear in two ways:
- You type a foreign key that never existed (99, 40).
- You delete a member who is still pointed at. Sophie still has loan 102 (Northern Light, 12 May 2026). If her Members row disappears, loan 102 still says MemberID 1, but row 1 is gone.
In both cases the pointer is dishonest. Overdue letters have no address. Shelf checks have no title.
The rule
Referential integrity says a foreign key must match a real primary key. Loans.MemberID must already exist in Members.MemberID. Loans.BookID must already exist in Books.BookID. No 99. No 40. No leftover loans after Sophie is removed — unless you have deliberately dealt with those loans first.
The error message is the rule working. It is not an inconvenience to switch off.
What the rule does when you type 99 or 40
The line is the relationship. Integrity is the rule on that line.
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.
Delete restrictions
Sophie Walsh of York still has loan 102. Someone tries to delete her member row.
If referential integrity is on (the sensible default), the software refuses the delete. She still has related loans. You return Northern Light, or you keep the member row as history.
If the rule is off, loan 102 remains, now pointing at a missing 1. That is an orphan. The desk cannot tell her a book is late.
Some software offers "also delete the loans". That is dangerous in a library: you would destroy the record of who borrowed Northern Light. Leave that option off unless you truly mean to erase the related rows.
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: Validation · Previous: Data Integrity and Invalid Data