Chapter 5: Data Validation Techniques
Referential integrity means every child record has a valid parent. Every order belongs to a real user. Every transaction points to a real account. Every review links to a real product. When this breaks, you get orphan records — data floating in the database with no parent, like a package with no delivery address.
A user deletes their account. The row in the users table disappears. But their 47 orders, 12 reviews, and 3 addresses still exist in child tables, all pointing to a user_id that no longer exists. Reports crash. The dashboard shows "Unknown User." The admin panel throws errors.
-- Find orphan orders (orders without a valid user)
SELECT o.order_id, o.user_id, o.total_amount, o.created_at
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
-- Find orphan order items (items without a valid order)
SELECT oi.item_id, oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;
-- Find orphan transactions (pointing to non-existent accounts)
SELECT t.transaction_id, t.from_account, t.to_account, t.amount
FROM transactions t
LEFT JOIN accounts a1 ON t.from_account = a1.account_id
LEFT JOIN accounts a2 ON t.to_account = a2.account_id
WHERE a1.account_id IS NULL OR a2.account_id IS NULL;
-- Generic template: Find orphans in ANY child table
-- SELECT child.* FROM child_table child
-- LEFT JOIN parent_table parent ON child.fk_column = parent.pk_column
-- WHERE parent.pk_column IS NULL;When a parent record is deleted, what happens to the children? That depends on the CASCADE setting. There are four options, and each one behaves differently. Testing cascade behavior is one of the most overlooked areas in database testing.
| Cascade Option | What Happens on Parent Delete | When to Use | Risk |
|---|---|---|---|
| CASCADE | Child records are deleted automatically | User deletes account, remove all their data | Accidental mass deletion |
| SET NULL | Child FK column is set to NULL | Employee leaves, keep their past work but remove link | Orphan-like records with NULL FK |
| SET DEFAULT | Child FK is set to a default value | Reassign to a default "system" user | Default value must exist |
| RESTRICT / NO ACTION | Parent delete is BLOCKED if children exist | Cannot delete a customer who has active orders | Delete fails — may confuse users |
-- Step 1: Check what cascade rules exist
SELECT
tc.constraint_name,
tc.table_name AS child_table,
kcu.column_name AS fk_column,
ccu.table_name AS parent_table,
rc.delete_rule,
rc.update_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
-- Step 2: Test CASCADE DELETE
-- Before: Count child records
SELECT COUNT(*) FROM orders WHERE user_id = 42;
-- Returns: 5
-- Delete the parent
DELETE FROM users WHERE user_id = 42;
-- After: Check child records
SELECT COUNT(*) FROM orders WHERE user_id = 42;
-- If CASCADE: Returns 0 (children deleted)
-- If RESTRICT: DELETE itself would have failed
-- If SET NULL: Returns 5, but user_id is now NULLAlways verify FK constraints exist by querying information_schema. I have seen projects where the developer defined relationships in the ORM code but forgot to create actual FK constraints in the database. The application works, but the database does not enforce anything.
Q: How do you test referential integrity in a database?
A: I test referential integrity in three ways. First, I query for orphan records using LEFT JOIN: SELECT child.* FROM child_table LEFT JOIN parent_table ON child.fk = parent.pk WHERE parent.pk IS NULL. Any results mean broken integrity. Second, I verify FK constraints exist by checking information_schema.table_constraints. Third, I test cascade behavior — I delete a parent record and verify the children are handled correctly (CASCADE deletes them, SET NULL nullifies the FK, RESTRICT blocks the delete). I also try inserting a child record with a non-existent parent ID to confirm the FK constraint rejects it.
Key Point: Referential integrity means every child record has a valid parent. Test for orphans with LEFT JOIN and verify cascade behavior on every FK.