Course contentsShow
← Data Science
Lesson 1156 of 2,14529. Data Wrangling WorkflowsFree lesson

Referential Integrity Validation

Ensuring foreign key relationships are maintained and references to other tables or datasets are valid.

Referential Integrity Validation

You'll learn how to verify that foreign key relationships remain valid when working with datasets in Python, ensuring every reference points to something that actually exists.

What is Referential Integrity?

Referential integrity means that relationships between tables stay consistent. When one table references another—like an orders table pointing to a customers table via customer_id—every customer_id in orders must actually exist in the customers table. No orphaned records, no broken links.

Think of it like a library catalog: every book checkout record must reference a real library card holder. If someone's card gets deleted but their checkout history remains, you have broken referential integrity.

Why This Matters in Data Science

Unlike databases with enforced foreign keys, datasets you load from CSVs, JSON, or web scraping often lack these guarantees. You might merge data from multiple sources where:

  • A transaction references a deleted customer
  • An order points to a non-existent product
  • A child record exists without its parent

These inconsistencies corrupt analyses, cause merge failures, and produce misleading results.

Validating Referential Integrity

Check that all foreign key values exist in the referenced table:

import pandas as pd

# Child table references parent table
orders = pd.DataFrame({'order_id': [1, 2, 3], 'customer_id': [101, 102, 999]})
customers = pd.DataFrame({'customer_id': [101, 102, 103]})

# Find orphaned records
valid_ids = customers['customer_id']
orphans = orders[~orders['customer_id'].isin(valid_ids)]

if not orphans.empty:
    print(f"Found {len(orphans)} orphaned orders:")
    print(orphans)

This catches the order with customer_id=999, which doesn't exist in the customers table.

Key Takeaway: Referential integrity validation ensures every foreign key reference points to an existing record, preventing broken relationships that compromise data quality and analysis accuracy.