Referential Integrity & Foreign Keys
SOXfree10 data-quality rules for referential integrity. Covers foreign key validation, orphan child detection, self-reference resolution, circular hierarchy detection, bridge table completeness, one-to-one cardinality, type/format matching, soft-delete reference checks, lookup/dimension mapping, and parent-child completeness.
Checks included (10)
Foreign Key Exists in Reference
Every non-null foreign-key value must match an existing primary key in the reference table. The most fundamental integrity check.
No Orphan Child Records
Child rows must reference a parent that exists (e.g., order_line -> order). Orphans break downstream joins and aggregations.
Self-Reference Resolves
Self-referencing keys (manager_id -> employee.id, parent_account -> account.id) must resolve to a row in the same table.
Bridge / Junction Table Completeness
In a many-to-many junction table, both foreign keys must resolve to their respective parent tables.
No References to Soft-Deleted Rows
Foreign keys must not point to parent rows flagged is_deleted = true / status = 'inactive'. A frequent logical-integrity gap.
No Circular Hierarchy
Detect cycles in a parent-child hierarchy (A->B->A). Cycles cause infinite recursion in tree traversals and rollups.
Parent Has Required Children
Where a parent must have at least one child (order -> >=1 line item, invoice -> >=1 detail), flag childless parents.
One-to-One Cardinality Holds
Where a 1:1 relationship is expected (e.g., user -> profile), the foreign key must be unique — no parent may have two children.
FK / PK Type & Format Match
Join keys must share data type, length and format across tables. Mismatches (e.g., string '007' vs int 7) silently fail joins.
Reference Code Mapped to Dimension
Code/lookup columns (status_code, country_code, product_code) must map to an active row in the governing dimension/lookup table.