What Referential Integrity Means
Referential integrity means that relationships between data in a database are consistent: every foreign key value in a child table must correspond to an existing primary key in a parent table.
In plain English: every order must belong to a customer that exists. Every invoice line must reference a product that exists. Every activity log must reference a user that exists.
When this relationship breaks, you have orphaned records — data that references something that no longer exists.
How Integrity Violations Happen
Cascading deletes not configured: A customer is deleted from the customer table. Their orders remain in the orders table, now referencing a customer_id that no longer exists. The orders are orphaned.
Import errors: A data import adds orders without first importing the customers they reference. Foreign key constraints would catch this — but many databases have constraints disabled for performance during imports.
Sohovi validates your dataset before it enters the warehouse — catching format errors, nulls, and duplicates at the source.
Manual database edits: A developer deletes a record directly from the database to fix a bug, without deleting the dependent records in child tables.
Migration errors: During a platform migration, customers and orders are migrated separately. Some customer records fail to migrate due to data errors. The orders that referenced them are now orphaned.
Detecting Integrity Violations
A SQL query finds orphaned records:
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
This returns all orders where the customer_id doesn't match any customer record. Each row is a referential integrity violation.
Sohovi finds gaps, duplicates, and format errors in your CRM data — so your team is working from records they can trust.
Run similar queries for every foreign key relationship in your schema.
Measuring Integrity
Integrity rate = (Records with valid references / Total records) × 100
A score of 100% means no orphaned records. A score below 100% means some records reference things that don't exist.
Preventing Integrity Violations
- Enable foreign key constraints in your database (they prevent integrity violations at write time)
- Use cascading deletes (or restricts) appropriately for each relationship
- Test data migrations for integrity before cutting over to the new system
- Run integrity checks after any bulk import or database operation
