Referential Integrity & Orphaned-Record Checks

📌 Definition:

📖 Detailed Explanation:

Foreign keys model relationships (orders.user_id → users.id). When enforced by the database, they prevent orphans automatically. But integrity breaks down when constraints are missing, disabled for performance, or bypassed by bulk imports and soft-delete logic — and then orphaned records accumulate, causing broken pages, failed joins, and incorrect reports. The tester's job is to detect these gaps. The standard technique is the anti-join: LEFT JOIN the child to the parent and filter WHERE parent.id IS NULL to list children with no valid parent (or the equivalent NOT EXISTS). This should be run after operations that could break links, especially deletes and migrations. Related checks include verifying cascade behavior (does deleting a user correctly delete or reassign their orders per the spec) and confirming soft-deletes don't leave active children pointing at a logically-deleted parent. These checks are high value because orphaned data often passes functional tests yet corrupts analytics and crashes edge-case flows.

🔑 Key Points:

• Referential integrity = every child FK points to an existing parent row
• Orphans arise from missing/disabled constraints, bulk imports, or soft-delete logic
• Detect with an anti-join: LEFT JOIN child→parent WHERE parent.id IS NULL (or NOT EXISTS)
• Run after deletes and migrations, which most often break links
• Also verify cascade/soft-delete rules match the spec (delete, reassign, or block)

🌍 Real-World Example:

📎 Code Example:

-- Orphaned orders: reference a user that doesn't exist
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- Orphaned line items: reference a missing order or product
SELECT oi.id
FROM order_items oi
LEFT JOIN orders o   ON o.id = oi.order_id
WHERE o.id IS NULL;

SELECT oi.id
FROM order_items oi
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.id = oi.product_id);

-- Soft-delete integrity: active orders pointing at a soft-deleted user
SELECT o.id
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.deleted_at IS NOT NULL AND o.status = 'confirmed';