Unit 05.04: Finding orphans and broken key promises

Unit ID: SQL-M05-U05 - Estimated active time: 14-17 minutes Objective: test whether the foreign-key relationships you rely on actually hold in the data.

A foreign key is a promise, not a guarantee

The schema says every order belongs to a customer. Whether that is true in the data is a separate question - deletions, imports, and migrations break it routinely.

SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- 0

Zero orphans. The promise holds here - and now you know rather than assume.

What a non-zero result would mean

If that query returned 47, you would have 47 orders referencing customers that do not exist. Every consequence matters:

An inner join to customers silently drops those 47 orders, so revenue reported by region is short.
A LEFT JOIN keeps them with NULL customer fields, so they land in an "Unknown" bucket.
Neither is wrong; they are different answers, and you must choose knowingly.

Report orphans rather than working around them. They almost always indicate an upstream problem that will keep producing bad rows.

Testing uniqueness, the other half of the promise

A key that is not unique breaks joins just as badly:

SELECT COUNT(*)                     AS rows,
       COUNT(DISTINCT customer_id)  AS distinct_ids
FROM customers;
-- 4812 | 4812

Equal, so customer_id genuinely identifies a row. If distinct_ids were lower, joining on it would fan out - and you would be back in Unit 05.03 without knowing why.

The three-query integrity check

Run these before trusting any join on an unfamiliar database:

-- 1. Is the parent key unique?
SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM customers;          -- 4812 | 4812

-- 2. Do all children point at a real parent?
SELECT COUNT(*) FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;                                          -- 0

-- 3. How many parents have no children? (not an error, but shapes your join choice)
SELECT COUNT(*) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;                                             -- 3812

Three queries, under a minute, and you now know the shape of the relationship instead of guessing.

Practice

Run the same integrity check for order_items against orders. What do you expect, and what would a surprise mean?

Check your answer

-- Parent key unique?
SELECT COUNT(*), COUNT(DISTINCT order_id) FROM orders;     -- 1000 | 1000

-- Orphan items?
SELECT COUNT(*) FROM order_items i
LEFT JOIN orders o ON o.order_id = i.order_id
WHERE o.order_id IS NULL;                                  -- 0

-- Orders with no items?
SELECT COUNT(*) FROM orders o
LEFT JOIN order_items i ON i.order_id = o.order_id
WHERE i.line_id IS NULL;                                   -- 0

All clean. A non-zero orphan count would mean item rows referencing deleted orders - revenue attached to nothing. Orders with no items would mean an order that cannot be itemised, which is worth asking about before it appears in a report.

Takeaway

Test the key promises before you rely on them: parent unique, no orphans, and know how many parents have no children. Three queries turn assumptions into facts.

---