DataslopeReferential Integrity
Understand how PostgreSQL keeps references valid and how delete or update actions express design choices.
A foreign key says one row points to another. Referential integrity is the guarantee that the pointer is not a lie.
If an order stores customer_id = 7, then customer 7 must exist. If a
comment stores post_id = 20, then post 20 must exist. The database
protects those promises so your design cannot quietly drift into broken
links.
The orphan-row problem
An orphan row is a child row that points to a parent row that does not exist.
Orphans are not just untidy. They break the meaning of the data. A join may lose the order. A report may count revenue without a customer. A UI may try to show details that do not exist.
Referential integrity says: every reference must point to something real.
Enforcement is part of the design
When you declare a foreign key, PostgreSQL checks inserts and updates on the child table. It also checks changes to the parent table. If deleting a parent would leave children pointing nowhere, the database needs a rule for what should happen.
That rule is not just a DBA setting. It expresses what the relationship means.
Quick check
What does referential integrity guarantee?
Every table has the same number of rows.
Every foreign key value points to an existing referenced row.
Every text value is spelled correctly.
Every primary key starts at 1.
Delete behavior is a design choice
Imagine deleting a customer who has orders. What should happen to the orders? PostgreSQL can enforce several designs.
The right answer depends on the meaning of the relationship.
RESTRICT: child rows must keep their parent
RESTRICT means the parent cannot be deleted while children still refer
to it. This is a good fit when child rows make no sense without the
parent, or when deleting history would be dangerous.
Examples: orders that must keep their customer, invoice lines that must keep their invoice, payments that must keep their account.
CASCADE: child rows belong entirely to the parent
CASCADE means deleting the parent also deletes the children. This is a
good fit when child rows are dependent details that should disappear
with the parent.
Examples: comments on a deleted draft, line items in a temporary cart, settings owned by a removed profile.
SET NULL: the child can survive without a parent
SET NULL means the child row remains, but the foreign key is cleared.
This only works when the foreign key column allows NULL.
Examples: a support ticket whose assigned employee left, or a blog post whose optional editor was removed.
Quick check
Which delete behavior best matches "a task should disappear when its temporary project is deleted"?
RESTRICT.
CASCADE.
SET NULL on a NOT NULL column.
No foreign key.
Watch CASCADE and RESTRICT differ
This block creates two parent-child designs. Deleting a draft cascades to its comments. Deleting an account with invoices is restricted.
The first delete removes dependent comments. The second delete is blocked because invoices are treated as records that must not lose their account.
ON UPDATE is the same kind of question
Primary keys should be stable, but natural keys sometimes change. ON UPDATE actions define what happens to child references if the parent
key changes.
The design lesson is the same: choose the behavior that matches the meaning of your data. If a key change would be dangerous, restrict it. If the child should follow the parent identity, cascade may make sense. If the relationship is optional, setting null may be appropriate.
Prefer stable keys
If you often need ON UPDATE CASCADE, ask whether the parent key is too
changeable. A generated surrogate key usually avoids that pressure by
keeping identity separate from editable real-world attributes.
How to choose
Use this decision path as a design conversation, not a mechanical rule.
Ask what the child row means without the parent. If it means nothing, cascade may be honest. If it must preserve history, restrict the delete. If it can exist unattached, set the reference to null.
Check your understanding
What is an orphan row?
A parent row with many children.
A child row whose foreign key points to a parent row that does not exist.
A row with a primary key.
A row that appears at the top of a result set.
When is ON DELETE RESTRICT a good design choice?
When children should always be deleted automatically with the parent.
When child rows must not lose their parent, so deleting the parent should be blocked.
When the foreign key should become null every time.
When there is no relationship between the tables.
What does ON DELETE CASCADE mean?
Deleting a child row deletes the whole database.
Deleting a parent sets every child foreign key to zero.
Deleting a parent row automatically deletes child rows that reference it.
Deleting a parent is always blocked.
What must be true for ON DELETE SET NULL to work?
The child foreign key column must be NOT NULL.
The child foreign key column must allow NULL.
The parent table must have no primary key.
The child row must be deleted first.
Why are ON DELETE and ON UPDATE actions design choices?
Because they change the font used in ER diagrams.
Because they decide whether SQL keywords are uppercase.
Because they define what the relationship means when referenced rows change or disappear.
Because PostgreSQL chooses a random behavior unless you draw a diagram.