Referential integrity

What is referential integrity?

Referential integrity is the database rule that a foreign key value must always point to a row that actually exists. If an order says it belongs to customer 4712, then customer 4712 has to be present in the customer table. The value cannot refer to a customer that was never created or was already deleted.

It is one of the core guarantees of a relational database. Primary keys give every row a unique identifier. Foreign keys let one table point at another. Referential integrity is the promise that those pointers never dangle.

Think of it like a library. Every loan record names a book by its catalogue number. Referential integrity means you can never register a loan for a catalogue number that is not in the catalogue.

What an orphaned record is

An orphaned record is a row whose foreign key points to a parent that is gone. An order line that references order 900, when order 900 no longer exists, is an orphan. The child survived, the parent did not.

Orphans are the exact problem referential integrity prevents. They cause reports that do not add up, joins that silently drop rows, and totals that disagree depending on which table you start from. Once a database is full of orphans, you can no longer trust that a count of order lines matches a count of orders.

How the database enforces it

You declare a foreign key constraint on the child table and name the parent table and column it must match. From that point the database checks every write.

The constraint blocks two kinds of mistake. You cannot insert or update a child row with a foreign key value that has no matching parent. And you cannot delete a parent row while children still point to it, unless you have told the database what should happen instead.

That second decision is set with a referential action:

• RESTRICT or NO ACTION. The delete or update is refused while any child still references the parent. This is the safe default for most business data.

• CASCADE. Deleting the parent deletes its children too, and updating the parent key updates the children. It suits true parent-child data such as an order and its order lines.

• SET NULL. The child keeps existing but its foreign key is emptied, which only works if the column is allowed to hold no value.

Here is a foreign key with a referential action in SQL:

CREATE TABLE order_line (
  id        INT PRIMARY KEY,
  order_id  INT NOT NULL,
  FOREIGN KEY (order_id)
    REFERENCES orders (id)
    ON DELETE CASCADE
);

With this in place, the database itself refuses an order line for an order that does not exist, and clears out the lines when an order is deleted.

Referential integrity in analytics systems

Operational databases behind a CRM, ERP or webshop lean on referential integrity heavily, because a wrong pointer there can break a live transaction. Analytical systems treat it differently.

Many data warehouses and lakehouses do not enforce foreign key constraints at all, or they declare them only as documentation that the engine never checks. The reason is speed: verifying every foreign key during a bulk load of millions of rows is expensive, and the data has usually been validated upstream already. In Power BI, relationships between a fact table and its dimensions play the referential role, and a blank row appears when a fact points to a dimension member that is missing.

So the guarantee still matters for analysis, it is just enforced earlier. Data quality tests and data reconciliation checks take over the job the constraint would have done in the source system.

What to watch out for with referential integrity

Loading order matters. You have to insert parents before children. Load all customers, then their orders. Bulk loads often disable constraints, load everything, then switch checks back on, which fails loudly if any orphan slipped in.

Cascading deletes can go further than you expect. One CASCADE can trigger another. Deleting a single customer might remove their orders, and then every order line under those orders. Map the chain before you rely on it.

Warehouses inherit orphans from the source. If the source system never enforced integrity, the mess arrives in your reporting layer intact. Profile the data and test for orphans instead of assuming the keys line up.

Soft deletes need their own rules. Marking a parent row as inactive rather than deleting it keeps the foreign key valid, but reports then have to know to filter the inactive parents out.