Referential Integrity

What Is Referential Integrity?

Referential integrity is the guarantee that relationships between records stay valid. In a relational database, an invoice header points to its line items, its customer, and its general ledger postings. Every one of those links has to resolve to a real record. A link that points at nothing is an orphan, and an orphan is data that has stopped making sense.

In a live system the database or the application enforces this. Archiving is where it breaks. Extract tables one at a time, on different days, from a system still taking transactions, and the pieces stop lining up. Records arrive without their parents. Codes arrive without the tables that decode them.

Older systems add a wrinkle. Many enforce relationships in application code rather than through database constraints, which means the database itself cannot tell you which tables belong together. The rules have to be recovered from the application’s logic and documentation, not just its schema.

Key Points

• Orphans are silent failures. A missing parent record does not raise an error during extraction. It surfaces months later when someone opens the archive and cannot read the transaction.
• Constraints may not exist in the database. Legacy applications often enforce relationships in code, so referential rules must be discovered, not just exported.
• Extract as a consistent set. Freezing the source and extracting related tables together prevents relationships from drifting mid-extraction.
• Code tables are part of the data. Values such as status codes and account types mean nothing without the lookup tables that translate them.
• It is verifiable. Post-load checks can test that every foreign key resolves, turning integrity from an assumption into evidence.

Common Use Cases

Financial transaction archiving

Journal entries, ledger lines, and supporting documents must remain linked, so an auditor can trace a balance back to source transactions.

Patient record retention

Encounters, orders, results, and provider records need to stay associated with the right patient, or the archive stops being clinically usable.

HR and payroll history

Employee, position, pay period, and deduction records connect intricate ways. Losing one link can make an earnings statement impossible to reconstruct.

Referential Integrity vs. Related Concepts

Integrity, completeness, and validity are related and distinct.

DATA COMPLETENESS

Every required record is present. Referential integrity means the records that are present are correctly linked to each other.

DATA VALIDATION

Checks data against rules, such as formats and permitted values. Referential integrity is one specific class of rule: that references resolve.

BUSINESS OBJECT ARCHIVING

Preserving related records together as a readable unit, such as an invoice and its lines. It depends on referential integrity being intact.