Referential integrity keeps every link valid. A child row must always have a real parent, and a parent cannot be removed while children still depend on it.
This lesson completes database development. It extends checking a foreign key against sample rows.
What are the two rules?
- No child without a parent. A foreign key value must exist as a primary key in the parent table.
- No parent removed while children remain. Delete or move the child rows first.
Inserting a new parent row never breaks either rule.
Worked example: a hostel database
Original Room (parent):
| RoomNo | Block |
|---|---|
| A01 | A |
| A02 | A |
| B01 | B |
Original Resident (child, RoomNo is the foreign key):
| ResidentID | Name | RoomNo |
|---|---|---|
| R1 | Aina | A01 |
| R2 | Bala | A01 |
| R3 | Chen | B01 |
Judge four operations.
| Operation | Allowed? | Reason |
|---|---|---|
| Insert resident R4, Dina, room A02 | yes | A02 exists in Room |
| Insert resident R5, Emir, room C05 | no | C05 is not in Room, so Emir would be an orphan |
| Delete room A01 | no | R1 and R2 still live there |
| Delete room A02 | yes | no resident refers to A02 |
To delete room A01, first move Aina and Bala to another room, or remove their rows, then delete A01.
The mistake that costs marks
Students say “the database will not allow it” without naming the rule. The answer earns more marks when the reason is explicit.
| Weak answer | Strong answer |
|---|---|
| “It is not allowed.” | “Room C05 does not exist in Room, so Emir’s foreign key would point to nothing.” |
The fix is to name the foreign key, the missing or remaining row, and the rule broken.
Check yourself
Using the tables above, judge: (a) insert resident R6 in room B01; (b) delete room B01; (c) insert room B02 with no residents.
Answer
(a) Allowed. B01 exists in Room.
(b) Not allowed. Chen (R3) still lives in B01, so deleting it would leave Chen pointing to nothing.
(c) Allowed. A new parent row with no children breaks neither rule.
What to study next
Test the whole cluster with the database development practice set. Then see how queries behave on small data in SQL reasoning in a fictional database.
If you want a teacher to go through integrity questions, see online one-to-one Computer Science tuition.