Nullable Foreign Keys That Quietly Break Reporting

Optional foreign keys are sometimes correct: an order may not yet have a courier assignment. They become hazardous when every consumer assumes the relationship always exists.

Patterns we flag in assessments

• Columns named *_id with no constraint and a 30% null rate that nobody can explain
• Soft-deleted parents still referenced by children that analytics joins treat as active
• Multiple nullable paths to the same concept (customer on the order and on the invoice) with divergent values

What to do before adding a constraint

Count orphans. Decide whether to backfill, quarantine, or accept nulls with an explicit domain rule. Adding a foreign key on dirty data fails loudly — which is better than failing silently in a board report, but only if you planned the cleanup.

A Schema Health Assessment is often enough to list these risks before a refactor budget is approved.

← All field notes