Data & Referential Integrity

1 The Hook

A fictional NZ insurer, Tasman Mutual, ran a routine job to tidy duplicate customer records. Two records for the same person — created when she was entered once as a married name and once as a maiden name — were merged. The job deleted the older duplicate customer row and kept the newer one. The screen showed one clean customer. Everyone moved on.

But that deleted customer row had three policies linked to it. The policies were stored in a separate policy table, each carrying the customer_id of the record that was now gone. The merge job deleted the customer but never re-pointed the policies at the surviving record. Nothing failed. No error appeared. The three policies simply now referenced a customer that no longer existed.

For months everything looked normal. Premiums kept being collected by a separate billing process that worked off policy IDs. Then a claim came in against one of those policies. The claims system tried to load the policyholder’s details, found a customer_id pointing at nothing, and could not identify who the policy belonged to. A valid, paid-up customer could not make a claim, because in the data she no longer owned her own policies.

Here is the lesson hidden in that story. The merge looked successful at every visible level — the screen, the premium collection, the duplicate count. The damage was a broken link between two tables, a row pointing at a parent that no longer existed. It threw no error because nobody had told the database to enforce the relationship, and nobody had tested for orphans afterwards. This whole class of defect — silent corruption of the links between data — is what this lesson is about.

2 The Rule

3 The Analogy

4 Constraints — the Database’s Own Rules

A constraint is a rule the database itself enforces on every write, refusing any change that would break it. Constraints are the system’s first line of defence against bad data — and when they are missing, the door to silent corruption is open. A tester checks both that the right constraints exist and that they actually bite.

The constraints that matter most:

• NOT NULL: the column must always have a value. An IRD number column marked NOT NULL means no account can be saved without one.
• UNIQUE: no two rows may share this value. A unique constraint on email, or on IRD number, is what stops the duplicate customer that started the Tasman story.
• PRIMARY KEY: the unique, never-null identifier of a row — UNIQUE and NOT NULL combined.
• FOREIGN KEY: a column whose value must match an existing row in another table — the constraint that would have stopped the Tasman orphans by refusing to delete a customer who still had policies. This is the heart of referential integrity, covered next.
• CHECK: a value must satisfy a rule — balance >= 0, or benefit_type IN (...). It stops nonsensical values at the door.

The tester’s job is twofold. First, confirm the expected constraints are actually defined — a UNIQUE rule that everyone assumed existed but was never created will not stop a single duplicate. Second, confirm each constraint rejects a bad write: try to insert a duplicate email, a NULL where one is forbidden, a foreign key pointing nowhere — and assert the database refuses it. A constraint that is documented but not enforced is worse than none, because the team trusts it.

5 Foreign Keys and Referential Integrity

Referential integrity is the guarantee that every reference between tables points at something real. A policy row carries a customer_id; referential integrity is the promise that this customer_id always matches an actual customer. The foreign key constraint is what enforces that promise.

When a foreign key is properly defined, the database physically prevents the Tasman defect. It will not let you delete a customer who still has policy rows pointing at them — the delete is refused, forcing the merge job to deal with the policies first. The constraint turns a silent corruption into a loud, immediate error at the moment of the bad write, which is exactly where you want the failure.

The trouble is that referential integrity is frequently not enforced. Foreign keys cost a little performance and a little flexibility, so on many real NZ systems they were never added, or were disabled during a bulk load and never re-enabled. When the constraint is absent, nothing stops a child row from being orphaned — and the only protection left is a tester who goes looking. So the first integrity question on any new system is: are the foreign keys actually enforced, or just implied by naming? If they are not enforced, every relationship is a place orphans can breed, and every one needs a test.

6 Orphaned Rows and Cascade Deletes

An orphaned row is a child row whose parent no longer exists — a policy whose customer was deleted, a payment whose order was removed, a claim line whose claim is gone. The defining query for an orphan is the LEFT JOIN from Lesson 1: keep every child, join to the parent, and find the children where the parent side is NULL.

Read it: “Every policy whose customer cannot be found.” The expected answer is zero rows. Every row that comes back is a Tasman-style orphan — a policy nobody owns. This one query is the entire test the insurer was missing.

The opposite risk is a cascade delete that deletes too much. A foreign key can be set to ON DELETE CASCADE, meaning when you delete a parent, the database automatically deletes all its children. This prevents orphans — but it is a loaded weapon. Delete one customer and every policy, every payment, every claim linked to them vanishes in a single statement, with no warning. On a system that holds records you are legally required to retain, a cascade delete can destroy data you were obliged to keep.

So a tester checks both ends. Where there is no cascade and no enforced foreign key, test for orphans after every delete or merge. Where there is a cascade, test that it deletes exactly what it should and nothing more — delete a parent and assert that only the intended children went, that no unrelated record was swept up, and that nothing the organisation must retain was destroyed. Both an orphan and an over-eager cascade are silent until someone notices the damage.

7 The Silent-Corruption Defect Class

What ties this lesson together is a single, dangerous property: these defects do not announce themselves. A crash is easy — it stops the line, someone investigates, it gets fixed. Silent corruption is the opposite. The system keeps running, screens keep rendering, batches keep completing, and the data is quietly wrong the whole time. The cost compounds, because every day the corruption sits there it spreads into reports, backups, and downstream systems that copy from it.

The members of this defect class share a shape:

• Orphaned rows — a child pointing at a parent that no longer exists (the Tasman policies).
• Duplicates — two rows where the business rule says there must be one: two customer records for one person, two payments for one order.
• Broken or inconsistent references — a status that does not match its allowed set, a foreign key that points at the wrong parent.
• Lost updates — a write that was supposed to update three tables and updated two, leaving the data internally disagreeing with itself.

Because none of these throws an error, you cannot test for them by exercising the application and watching for failures. You test for them by querying the data and asserting an invariant — a rule that must always hold. “No policy has a missing customer.” “No person has two active customer records.” “Every payment’s order exists.” Each invariant becomes a query whose correct answer is zero rows. Running that set of invariant queries after any operation that moves or deletes data — a merge, a migration, a bulk load, a cleanup job — is how you catch silent corruption while it is still one row, not one million.

8 Building Integrity Test Cases

An integrity test case names the invariant it protects, gives the query that detects a violation, and states that the passing result is zero rows. It is run as a guard around the risky operation — before and after.

Here is an integrity test case written to catch the exact Tasman defect:

Notice the shape: the invariant is stated in plain language, the detection query makes “is the invariant violated?” answerable, and the pass criterion is the unambiguous “zero rows”. Running it both before and after the merge matters — a clean “after” only proves the job did no harm if the “before” was also clean. And it traces to a numbered risk, so a reviewer can see why this invariant earns a guard.

9 Common Mistakes

10 Now You Try

Three graded exercises: spot the integrity risks, fix the migration that breaks links, then build an orphan-hunting query. Write your answer, run it for AI feedback, then compare to the model answer.

There are at least four real risks; any three well-explained earns full marks.

1. Duplicate claimants — no UNIQUE constraint on ird_number, and the same person is entered by two branches, so one person can have two claimant_id records. Defect type: duplicate / missing UNIQUE constraint. Silent because both records are individually valid; nothing rejects the second one, and claims may scatter across both identities.

2. Orphaned claims — claimant_id on claim is named like a key but has no foreign-key constraint, so a claim can point at a claimant_id that does not exist (or is later deleted). Defect type: orphan / missing foreign key. Silent because the database never refuses the bad reference; the claim looks fine until someone tries to load the claimant.

3. Cascade blast radius — claim_payment cascades on delete from claim, and the admin tool deletes a claim with no check for existing payments. Deleting a "mistake" claim silently destroys every payment record linked to it. Defect type: cascade blast radius. Silent because the delete succeeds with no warning and the payment history — which ACC may be required to retain — is gone.

Bonus: a deleted claim with an enforced FK would be refused while payments exist; here nothing stops it, compounding the cascade risk.

The thread: every one of these throws no error. They are caught only by querying for orphans, duplicates, and by testing the delete's blast radius before trusting the tool.

There are two serious bugs, and they compound.

Bug 1 — Wrong order. Step 1 deletes the duplicate patients BEFORE step 2 re-points the appointments. So at the moment of the delete, every appointment still points at a patient_id that is about to vanish. Any appointment whose patient is deleted is now an orphan, and step 2 cannot reliably fix what step 1 already broke. The re-point MUST happen before the delete.

Bug 2 — The UPDATE is nonsensical. SELECT MIN(patient_id) FROM patient GROUP BY nhi_number returns one row per NHI, but the UPDATE has no correlation to the appointment's own NHI — it sets EVERY appointment to the same single value (or errors as a multi-row subquery). It does not map each appointment to ITS patient's kept record.

Corrected migration — re-point first, mapping each appointment to the kept patient for its OWN NHI, then delete:

-- Step 1: re-point each appointment to the surviving patient for the same NHI
UPDATE appointment a
SET patient_id = (
  SELECT MIN(p2.patient_id)
  FROM patient p1
  JOIN patient p2 ON p2.nhi_number = p1.nhi_number
  WHERE p1.patient_id = a.patient_id
);

-- Step 2: now it is safe to delete the duplicates
DELETE FROM patient
WHERE patient_id NOT IN (
  SELECT MIN(patient_id) FROM patient GROUP BY nhi_number
);

-- Step 3 (the test): assert zero orphans
SELECT a.appointment_id FROM appointment a
LEFT JOIN patient p ON p.patient_id = a.patient_id
WHERE p.patient_id IS NULL;  -- must return 0 rows

The lesson: re-point children before deleting parents, map each child to its own parent, and finish with an orphan-detection query as the pass gate.

(a) Orphaned accounts — accounts whose customer is missing:
SELECT a.account_id, a.customer_id
FROM   account a
LEFT JOIN customer c ON c.customer_id = a.customer_id
WHERE  c.customer_id IS NULL;
PASS result: 0 rows.

(b) Orphaned transactions — transactions whose account is missing:
SELECT t.txn_id, t.account_id
FROM   transaction t
LEFT JOIN account a ON a.account_id = t.account_id
WHERE  a.account_id IS NULL;
PASS result: 0 rows.

(c) Duplicate customers by IRD number:
SELECT ird_number, COUNT(*) AS n
FROM   customer
WHERE  ird_number IS NOT NULL
GROUP BY ird_number
HAVING COUNT(*) > 1;
PASS result: 0 rows.

Why each works: (a) and (b) use a LEFT JOIN and filter for the parent key being NULL — an inner join would hide the very orphans you want. (c) groups by the value that must be unique and keeps only groups with more than one row; any row returned is a duplicate IRD. The WHERE ird_number IS NOT NULL avoids grouping all the missing IRDs together as a false duplicate.

Run all three as a set after any merge, load, or cleanup. Together they assert the three core invariants: every account has a customer, every transaction has an account, and no IRD is shared. Each correct answer is zero rows.

11 Self-Check

Click each question to reveal the answer.

12 Interview Prep

Real questions asked in NZ QA interviews for data-heavy roles. Read the model answers, then practise your own version.