Stop 05 · SQL & Databases for QATopics · 8/8

Data Integrity Testing

In short

An order pointing to a deleted user, two accounts with the same email, a total that doesn't match its items. Data integrity testing finds these quiet problems before they become loud ones.

Verifying that relationships between data remain consistent — no orphaned records, no broken foreign key references, no duplicate entries where uniqueness is required.

The purple blob at a small library desk compares a big register book with a bookshelf beside it.

Think of it like this

The library issue register

Every line in the library register must point to a real member and a real book. A line for member 450 when there's no member 450 is a problem. So is one book issued to two people at once.

Watch it happen

orders.user_id vs real users (7, 12, 15)

order 1 -> 7

order 2 -> 12

order 3 -> 99

order 4 -> 15

order 5 -> 7

1/4Each order points to a user id. The users table only has ids 7, 12 and 15.

The big ideas

What you need to know

No orphans

Every foreign key value must match a real row in the parent table.

No unwanted duplicates

Emails, phone numbers or order references that must be unique should appear once.

Totals add up

An order total should equal the sum of its items, minus discounts, plus fees.

Deletes cascade correctly

Deleting a parent should delete, block or keep children exactly as the design says.

See it in code

Walk through the code

sql

1-- Orphans: orders whose user no longer exists2SELECT o.id, o.user_id3FROM orders o4LEFT JOIN users u ON u.id = o.user_id5WHERE u.id IS NULL;6 7-- Duplicates: emails that should be unique8SELECT email, COUNT(*) AS copies9FROM users10GROUP BY email11HAVING COUNT(*) > 1;

1/3LEFT JOIN keeps every order, even ones with no matching user.

Two classic integrity checks. Both should return zero rows on a healthy database.

Save yourself the pain

Common mistakes

Oops

Assuming the database enforces every rule automatically.

Do this instead

Many schemas lack foreign keys or unique constraints. Query for violations instead of assuming.

Oops

Checking integrity only after your own test steps.

Do this instead

Run integrity sweeps after imports, migrations and bulk jobs too. That's when damage happens.

Your mission

Plant and catch integrity bugs

~30 min

Quick check

Which query pattern finds orphan child rows?

Go deeper

1Write a query to find orphaned records (e.g. orders referencing a deleted customer).
2Learn why data integrity issues often surface as confusing, hard-to-reproduce application bugs.
3Practice designing a test scenario specifically targeting a data integrity constraint.