Integrity and Foreign Keys
Make every join contractually sound
Referential integrity is the contract between tables: every foreign key points at an existing parent. Checking it before the join is what separates sound analytics from silent orphans and phantom counts.
What you will learn
- Check FK values against parent keys
- Classify orphans: legit gaps vs real dirty
- Verify cardinality before designing joins
What is this?
Referential integrity means a child's foreign key always resolves to a parent record. Checks: set difference of child keys vs parent keys (orphans), parent-key uniqueness (cardinality 1:many), and cross-table agreement on key dtypes. Orphans can be legitimate (orders for customers deleted by GDPR) or dirty — deciding which is the analyst's call before joins corrupt numbers.
Why do you need it?
- Joining on a raggedy FK silently drops or duplicates whole populations.
- Cardinality misreads (assuming 1:1 when it's 1:many) are the top join bug.
- Orphans that look like data loss can be legitimate exclusions — know which.
- Integrity checks are the cheapest 'tests' a cleaning pipeline can run.
Mental model
A registry office cross-checking birth certificates against citizens
Foreign key checks
Every certificate must reference an existing citizen ID; a record pointing to a missing citizen is either an error or a deliberate removal — but the registry can't just pretend it's fine. Analysts check exactly that before slotting records together.
How it works
- orphans = set(child['fk']) - set(parent['id']); report the count and samples.
- cardinality = parent.groupby('id').size() — check max(size) and duplicated() on the key.
- dtype alignment: int vs str keys match nothing; cast before diffing.
- Decide per orphan group: exempt (legit gap), fix (data error), or exclude with a flag.
- Document the integrity contract: which column references which key at which cardinality.
Integrity pipeline before the join.
Examples
Audit before you join
import pandas as pdparents = pd.DataFrame({"customer_id": [1, 2, 3], "plan": ["pro", "basic", "pro"]})children = pd.DataFrame({"customer_id": [1, 3, 3, 404], "order": [10, 20, 30, 40]}) orphans = children[~children.customer_id.isin(parents.customer_id)]print("orphans:", orphans.customer_id.unique().tolist())card = parents.groupby("customer_id").size()print("cardinality ok:", (card <= 1).all(), "| dup keys:", int(parents.duplicated(subset=['customer_id']).sum()))The diff exposes order rows for customer 404 before any damage; the parents' cardinality check runs at the same time.
In production
- A reporting layer fails its integrity test when orphans exceed 0.1% of rows.
- An ETL documents every FK contract so schema changes can't silently break joins.
- A consent-law migration flags deleted-customer orphans as expected and excludes them deliberately.
Best practices
- Run integrity checks in the cleaning step, before any join exists to exploit them.
- Record cardinality intentions explicitly in the data contract.
- Align key dtypes early — int vs string is the classic silent-KO.
- Tag expected orphans (deletions) so real dirt is distinguishable.
Common mistakes
- Joining without checking orphans, then 'why is my population smaller?'.
- Assuming 1:many where the parent is duplicated (the fan-out classic).
- Diffing int keys against string keys and seeing zero matches.
- Treating every orphan as an error when some are sanctioned exclusions.
Debugging tips
- Counts shrink after a join → orphan side found; diff the keys set-wise.
- Counts explode → parent key not unique; check duplicated on the parent before joining.
- A join yields nothing at all → dtype mismatch; print dtypes of both keys.
- Orphan time-travel (orders older than customers) → look for soft-deleted or re-imported parents.
Interview checkpoint
Quick quiz
An orphan in a join means:
Joining on int keys against string keys is safe because pandas coerces silently.