A foreign key is valid when every value in it appears as a primary key value in the parent table. Check this by matching, row by row.
This lesson belongs to relational data modelling. Its rules are applied to changes in checking referential integrity in examples.
What are the three matching steps?
- List the primary key values of the parent table.
- Read each foreign key value in the child table.
- Tick it if it is in the list, and circle it if it is not.
A circled value is an orphan. The link it claims to make does not exist.
Worked example: library loans
Book (parent table)
| BookID | Title |
|---|---|
| B1 | Atlas |
| B2 | Dictionary |
| B3 | Novel |
Loan (child table, BookID is the foreign key)
| LoanID | MemberID | BookID |
|---|---|---|
| L1 | M01 | B1 |
| L2 | M02 | B3 |
| L3 | M01 | B9 |
Step 1. Parent keys: B1, B2, B3.
Step 2 and 3. L1 has B1, which matches. L2 has B3, which matches. L3 has B9, which is not in the list, so it is an orphan.
The loan L3 claims that member M01 borrowed a book that the library does not list.
How can I fix an orphan?
There are two honest fixes. Either the book B9 was left out, so add B9 to Book. Or the ID was mistyped, so correct L3 to the right BookID.
Deleting a row is also possible, but only when the loan itself was a mistake.
The mistake that costs marks
Students check that the column name matches, for example that both tables have a BookID column, and stop. They never compare the actual values.
| Check | Catches orphan B9? |
|---|---|
| column names match | no |
| every value appears in the parent table | yes |
The fix is to compare values, not headings.
Check yourself
Class has ClassID values 4A and 4B. Student rows have ClassID 4A, 4B, 4A and 4C. Which rows are valid, and what is wrong?
Answer
The first three rows match 4A, 4B and 4A, so they are valid. The fourth row has 4C, which is not in Class, so it is an orphan. Either add class 4C to Class or correct the student’s ClassID.
What to study next
Apply the matching check to insertions and deletions. Continue with checking referential integrity in examples, then try the relational data modelling practice set.
If you want a teacher to check your tables with you, see online one-to-one Computer Science tuition.