Foreign keys and referential integrity

A foreign key is a column holding another table's primary key. It is how the relational model connects tables — by value, at query time — and it is the constraint that makes nonsense data impossible.

Declaring one

CREATE TABLE loans (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id     bigint NOT NULL REFERENCES books(id),
  member_id   bigint NOT NULL REFERENCES members(id),
  borrowed_on date   NOT NULL
);

REFERENCES books(id) says: every value in book_id must exist in books.id.

REFERENCES books without the column name also works — it uses the primary key. Name it anyway; explicit beats implicit when somebody reads this in a year.

The two guarantees

You cannot point at a row that does not exist:

INSERT INTO loans (member_id, book_id, due_date) VALUES (999, 1, DATE '2026-10-14');

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL:  Key (member_id)=(999) is not present in table "members".

And you cannot delete a row that is pointed at:

DELETE FROM members WHERE id = 1;

ERROR:  update or delete on table "members" violates foreign key constraint "loans_member_id_fkey" on table "loans"
DETAIL:  Key (id)=(1) is still referenced from table "loans".

Together these mean orphan rows cannot exist. A loan always has a real book and a real member, in every environment, from every client, forever.

Without the constraint, a loan pointing at book 999 is perfectly storable, and you discover it months later when a join silently returns fewer rows than expected.

NOT NULL is a separate decision

member_id bigint NOT NULL REFERENCES members(id)   -- required
publisher_id bigint REFERENCES publishers(id)      -- optional

The foreign key says "if there is a value, it must exist". It says nothing about whether there must be a value.

A nullable foreign key means the relationship is optional. A book may have no publisher recorded. A loan must have a member — a loan to nobody is meaningless.

Decide the two separately, and write both.

The one-to-many shape

This is the shape you will build most:

members                loans
  id  ◀────────────┐     id
  name             └───  member_id
  email                  book_id
                         borrowed_on

The foreign key lives on the "many" side. One member has many loans, so loans holds member_id. There is no loan_ids column on members, and there cannot be — a column holds one value.

That is worth stating because it is the first thing that feels backwards coming from objects, where a Member would naturally hold a list of loans.

If you find yourself wanting a list in a column, you want either a foreign key on the other side, or a join table — two lessons away.

What happens on delete

REFERENCES members(id)                       -- NO ACTION: refuse. The default.
REFERENCES members(id) ON DELETE RESTRICT    -- refuse, checked immediately
REFERENCES members(id) ON DELETE CASCADE     -- delete the referencing rows too
REFERENCES members(id) ON DELETE SET NULL    -- blank it (the column must be nullable)
REFERENCES members(id) ON DELETE SET DEFAULT

The default — refuse — is usually right, because it forces a person to decide what should happen rather than having the database decide silently.

The difference between NO ACTION and RESTRICT only matters in a deferred constraint: NO ACTION checks at the end of the statement, RESTRICT immediately. In practice, use the default.

CASCADE is right when the child cannot exist alone:

CREATE TABLE book_categories (
  book_id     bigint REFERENCES books(id)      ON DELETE CASCADE,
  category_id bigint REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, category_id)
);

A category link is meaningless without its book. Deleting the book should remove the link.

CASCADE is dangerous when the child is a record in its own right. ON DELETE CASCADE from members to loans would let one DELETE erase a member's entire borrowing history in silence. And cascades chain: delete a member, cascade to loans, cascade to loan_events, and one statement has removed rows from three tables.

Two habits worth having:

Know your cascade graph. Before deleting from any table with children, know what goes with it.

Prefer a soft delete for records that matter. deleted_at timestamptz and a WHERE deleted_at IS NULL in your queries, rather than removing the row. Slower and you keep the history — and the audit trail, which for a loan is the point.

ON UPDATE CASCADE exists too, for when the referenced key changes. If you followed the primary-key lesson and used a surrogate key, it never changes and you never need this. Its existence is mostly an argument for surrogate keys.

Index your foreign keys

PostgreSQL indexes the referenced column automatically — it is a primary key. It does not index the referencing column.

CREATE INDEX ON loans (member_id);
CREATE INDEX ON loans (book_id);

Two consequences without them:

Deletes from the parent get slow. Every DELETE FROM members must scan the whole loans table to check for references.

Joins in that direction get slow, which is most of your joins.

Index every foreign key. It is close to a rule. Module 7 explains what an index is; the habit is worth forming now.

Finding them

\d loans

The bottom of the output lists both directions — what this table references and what references it. That is the fastest way to understand an unfamiliar schema: pick a table and read its foreign keys.

-- every foreign key in the database
SELECT conrelid::regclass AS child, confrelid::regclass AS parent, conname
FROM pg_constraint WHERE contype = 'f' ORDER BY 1;

When people leave them out

You will meet schemas with no foreign keys, and the arguments for that are worth knowing so you can weigh them.

"The ORM handles it." It handles what goes through the ORM. The import script, the admin panel and the analyst do not.

"They slow down writes." Each check is an indexed lookup — measurable, tiny, and far cheaper than finding orphan rows a year later.

"We shard, so the parent is on another machine." This one is legitimate. Distributed systems genuinely cannot always enforce referential integrity, and they pay for it with reconciliation work. If you are not sharding, it does not apply to you.

"We load data in an order that breaks them." Solvable: load parents first, or use DEFERRABLE INITIALLY DEFERRED so the check happens at COMMIT, or briefly ALTER TABLE ... DISABLE TRIGGER ALL for a bulk load and re-enable afterwards.

Default to having them. The cost is small and the failure mode without them is data you cannot trust.

Check your work

What a foreign key says. Every value here exists in that column over there.

The two guarantees. Cannot reference a missing row; cannot delete a referenced row.

What it does not say. Whether a value is required — that is NOT NULL, decided separately.

Which side holds the foreign key. The many side.

Why there is no list column on the one side. A column holds one value.

The default ON DELETE, and why. Refuse — it forces a person to decide.

When CASCADE is right. When the child is meaningless without the parent.

Two dangers of CASCADE. It is silent, and it chains across tables.

The alternative for records that matter. A soft delete.

Why ON UPDATE CASCADE is rarely needed. A surrogate primary key never changes.

What PostgreSQL does not index for you. The referencing column.

Two things that get slow without that index. Deletes from the parent, and joins.

The one legitimate reason to omit foreign keys. Sharding across machines.

Practice

1. Look at \d loans and list both directions of its foreign keys.
2. Insert a loan with a book_id that does not exist. Read the whole error.
3. Delete a member who has loans. Read that error.
4. Create a publishers table and add a nullable publisher_id to books. Insert a book with no publisher.
5. Make publisher_id NOT NULL and try again.
6. Recreate the loans foreign key with ON DELETE CASCADE. Count loans, delete a member, count again.
7. Do the same with ON DELETE SET NULL and note what the column has to allow.
8. Draw the cascade graph for the six tables in this database.
9. Add a deleted_at column to members and write the soft-delete version of removing one.
10. Check whether loans.member_id is indexed. Time a DELETE FROM members before and after adding one.
11. Run the pg_constraint query and list every foreign key in the database.
12. Try to load loans before members exists. Then use DEFERRABLE INITIALLY DEFERRED.
13. Find a schema without foreign keys — many public ones qualify — and write down what could go wrong.

Official documentation

• PostgreSQL — Foreign keys — Declaration, the referential actions, and the note that the referencing column is not indexed for you.
• PostgreSQL — CREATE TABLE — The full REFERENCES syntax including DEFERRABLE.
• PostgreSQL — pg_constraint — The catalogue table behind the query above.

Next: INNER JOIN.