You moved all the data. Counts match. Values look right. But here is the question nobody asks until it is too late: do the relationships still work? Does every order still point to a valid customer? Does every transaction still link to a valid account? If not, your application will crash the moment someone tries to view their order history.
Referential integrity means every foreign key points to an existing primary key. If the orders table has customer_id = 42, then customer 42 must exist in the customers table. If it does not, that is an orphan record — and orphan records break applications.
-- Check 1: Orders with invalid customer_id
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
-- Expected: 0 rows. Any rows = broken relationship.
-- Check 2: Transactions with invalid account_id
SELECT t.txn_id, t.account_id
FROM transactions t
LEFT JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_id IS NULL;
-- Check 3: Addresses with invalid user_id
SELECT addr.address_id, addr.user_id
FROM addresses addr
LEFT JOIN users u ON addr.user_id = u.user_id
WHERE u.user_id IS NULL;
-- Check 4: Order items with invalid order_id or product_id
SELECT oi.item_id, oi.order_id, oi.product_id
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id IS NULL OR p.product_id IS NULL;Orphans happen when a child record exists but its parent does not. In a migration, this typically occurs when the parent table migration script fails or filters out some records, but the child table migration includes everything.
-- Comprehensive orphan scan across all relationships
SELECT 'orders -> customers' AS relationship,
COUNT(*) AS orphan_count
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT 'transactions -> accounts',
COUNT(*)
FROM transactions t
LEFT JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_id IS NULL
UNION ALL
SELECT 'order_items -> orders',
COUNT(*)
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL
UNION ALL
SELECT 'order_items -> products',
COUNT(*)
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE p.product_id IS NULL;Foreign keys are not just about data — they are database constraints that prevent bad data from entering in the future. After migration, verify that these constraints were actually created in the target database.
-- List all foreign keys in the target database
SELECT
CONSTRAINT_NAME,
TABLE_NAME AS child_table,
COLUMN_NAME AS child_column,
REFERENCED_TABLE_NAME AS parent_table,
REFERENCED_COLUMN_NAME AS parent_column
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'target_db'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;
-- Compare constraint count: source vs target
SELECT
'source' AS db,
COUNT(*) AS fk_count
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'source_db'
AND REFERENCED_TABLE_NAME IS NOT NULL
UNION ALL
SELECT
'target',
COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'target_db'
AND REFERENCED_TABLE_NAME IS NOT NULL;
-- Check NOT NULL constraints
SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'target_db'
AND IS_NULLABLE = 'YES'
AND COLUMN_NAME IN ('customer_id', 'account_id', 'email', 'status')
ORDER BY TABLE_NAME;Some migration tools disable foreign key checks during migration for performance (SET FOREIGN_KEY_CHECKS = 0). Always verify they were re-enabled after migration. If they are still off, the database will happily accept orphan records going forward.
Q: How do you verify referential integrity after a data migration?
A: I check three things: (1) Orphan detection — for every parent-child relationship, I LEFT JOIN the child table to the parent and look for NULLs. Any row where the parent is NULL means the foreign key points to a non-existent record. (2) Constraint verification — I query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list all foreign keys in the target database and compare with the source. Missing constraints mean the database will not prevent future integrity violations. (3) I verify that FOREIGN_KEY_CHECKS were re-enabled after migration, since some tools disable them for performance. I also check NOT NULL constraints on critical columns to ensure they were preserved.
Key Point: After migration, verify every foreign key relationship by checking for orphan records. Also confirm that FK constraints, NOT NULL constraints, and foreign key checks were preserved in the target database.