Checking Existing Data for Integrity
You'll learn how to audit existing tables for referential integrity problems before adding foreign key constraints.
When working with a database that doesn't yet have foreign key constraints—or where data was imported from external sources—you can't simply add a foreign key and hope for the best. SQL will refuse to create the constraint if existing data violates referential integrity. You need to audit first, clean second, then constrain.
Why Check Before Constraining?
Think of it like trying to lock a door that has boxes blocking it. You can't install the lock until you clear the obstruction. Similarly, you can't add a FOREIGN KEY constraint if "orphaned" rows exist—child records pointing to parent keys that don't exist.
Auditing Techniques
1. Find orphaned records with NOT IN:
SELECT *
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM customers);
2. Find orphaned records with NOT EXISTS:
SELECT *
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM customers c
WHERE c.customer_id = o.customer_id
);
3. Count integrity violations:
SELECT COUNT(*) AS orphaned_orders
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
Once you've identified violations, you can decide whether to delete orphaned rows, update them to valid references, or add missing parent records before applying the constraint.
Key Takeaway: Always audit existing data for referential integrity violations using NOT IN or NOT EXISTS queries before attempting to add foreign key constraints—SQL won't let you create constraints on dirty data.