A foreign key is the constraint that turns a pile of independent tables into a relational graph: it tells the database that a column in one table must always point at a real row in another. Without foreign keys you can write orders.user_id = 999 even when no user 999 exists, and nothing stops you — until a JOIN returns ghost rows, a billing job crashes on a NULL, or an analytics query silently undercounts. With foreign keys the database refuses the bad write at the boundary, so every other layer can trust that relationships are intact. This article covers what FKs actually guarantee, every ON DELETE / ON UPDATE action and when each is correct, why FK columns need their own indexes, deferrable constraints for circular references, and the safe migration pattern for adding FKs to a populated production table.
For foundational transaction guarantees, see ACID Properties; for combining tables across foreign keys, see PostgreSQL JOINs.
Without foreign keys, "Alice's orders" is a convention enforced only by application code. Every insert, update, and delete that touches a relationship is an opportunity for a bug to leave the database inconsistent: an order whose user_id points at a deleted user, a comment whose post_id typo doesn't match anything, a team_id left over after the team itself was dropped. These orphan rows are silent — they sit in the table looking valid until something joins through them and returns wrong answers, or worse, until a CASCADE you forgot to write deletes far more than intended. A foreign key moves enforcement from "every code path that mutates this table" to "the database itself, on every write, forever."
Imagine two filing cabinets: "Users" and "Orders." Every order folder has a slip with a user-ID written on it. A foreign key is the rule that the front-desk clerk enforces: whenever someone tries to file a new order, the clerk walks to the Users cabinet and checks that the referenced user folder actually exists. If it doesn't, the order is refused. When someone tries to throw out a user folder, the clerk checks the Orders cabinet first — depending on the rule, the clerk either refuses the deletion (RESTRICT), throws out every order belonging to that user too (CASCADE), or replaces the user-ID slips with blanks (SET NULL).
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id), -- the foreign key
amount NUMERIC(10,2) NOT NULL
);
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO orders VALUES (101, 1, 50.00); -- OK: user 1 exists
INSERT INTO orders VALUES (102, 999, 25.00); -- ERROR 23503: insert violates FK
The second insert fails with PostgreSQL error code 23503 (foreign_key_violation). The constraint is checked synchronously, inside the transaction, before any row is written.
Referential integrity is the invariant that every foreign-key value either matches an existing primary-key (or unique) value in the referenced table, or is NULL (when the column is nullable). The database enforces this on every INSERT, UPDATE, and DELETE that could violate it — there is no race condition where a window of inconsistency exists, even under concurrent transactions, because FK checks acquire row-level locks on the referenced parent row.
The guarantee is symmetric. Two things can break referential integrity:
ON DELETE, covered below.A foreign key can span multiple columns when the referenced primary key is composite. This shows up in multi-tenant schemas where a child row must point at a parent within the same tenant.
CREATE TABLE projects (
tenant_id UUID,
project_id UUID,
name TEXT NOT NULL,
PRIMARY KEY (tenant_id, project_id)
);
CREATE TABLE tasks (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL,
project_id UUID NOT NULL,
title TEXT NOT NULL,
FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, project_id)
);
The composite FK guarantees not only that the project exists but that it belongs to the same tenant — preventing cross-tenant data leaks at the schema level.
A foreign key can reference its own table — the canonical example is an org chart where every employee has a manager_id that points at another employee.
CREATE TABLE employees (
id INT PRIMARY KEY,
name TEXT NOT NULL,
manager_id INT REFERENCES employees(id) -- nullable: the CEO has no manager
);
Self-references force two design decisions: the column must be nullable (someone has to be the root), and you must decide whether cycles are allowed. Postgres does not enforce acyclicity — preventing manager_id cycles requires a CHECK based on a recursive CTE or a trigger.
The most consequential part of a foreign-key declaration is what happens when the referenced parent row is deleted or its key is updated. Postgres offers five actions; pick the wrong one and you get either crashes (orphans, RESTRICT panics) or silent data loss (runaway CASCADE).
The default if you write nothing. Postgres checks at the end of the statement (or at COMMIT, if the constraint is DEFERRABLE) that no children reference the about-to-be-deleted parent. Functionally identical to RESTRICT in non-deferrable mode — most teams treat them as synonyms.
user_id INT REFERENCES users(id) -- NO ACTION (implicit)
Same outcome as NO ACTION, but the check is immediate — it cannot be deferred. Use this when you want to forbid deferral even within a transaction that marks other constraints as deferred.
user_id INT REFERENCES users(id) ON DELETE RESTRICT
When the parent row is deleted, every child row that references it is deleted too — and if those children themselves have CASCADE-children, the cascade continues. This is the right default for owned relationships: a comments table owned by posts, order_items owned by orders, sessions owned by users. It is the wrong choice when the child rows have independent meaning that should outlive the parent (audit logs, financial records, soft-deletable entities).
post_id INT REFERENCES posts(id) ON DELETE CASCADE
When the parent is deleted, the FK column in every child becomes NULL. The column must be nullable for this to be legal. Use this when the relationship is informational rather than structural — e.g. tickets.assigned_to referencing users.id: deleting the user shouldn't delete the ticket, just unassign it.
assigned_to INT REFERENCES users(id) ON DELETE SET NULL
Sets the FK column to its column default — which itself must point at a row that exists, or the action fails. Common pattern: a category_id whose default points at an "Uncategorized" sentinel row that you guarantee never gets deleted.
category_id INT NOT NULL DEFAULT 0
REFERENCES categories(id) ON DELETE SET DEFAULT
This is the rarest action in production schemas — most teams reach for SET NULL or CASCADE instead. It exists for cases where NULL would be awkward in queries and a sentinel row is preferable.
| Action | Parent delete blocked? | Child row deleted? | FK column changed? | Typical use |
|---|---|---|---|---|
NO ACTION | yes (deferrable) | no | no | default; most strict relationships |
RESTRICT | yes (immediate) | no | no | when deferral must be forbidden |
CASCADE | no | yes | n/a | owned relationships (post→comments) |
SET NULL | no | no | → NULL | optional links (ticket→assignee) |
SET DEFAULT | no | no | → DEFAULT | sentinel-row patterns (category fallback) |
The same five actions are available for ON UPDATE, triggered when the parent's primary-key value is updated. In practice, ON UPDATE CASCADE is uncommon because most production schemas use immutable surrogate keys (UUIDs, serial IDs) that never change.
Postgres automatically creates an index on the referenced column (the parent's primary key — already indexed by definition) but it does not create one on the referencing column. This is the single most common foreign-key footgun in production: every DELETE or UPDATE on the parent has to scan every child table whose FK points at it, looking for matching rows. Without an index on the FK column, that scan is a sequential scan over the entire child table.
-- Without this, deletes from users will seq-scan orders every time:
CREATE INDEX orders_user_id_idx ON orders (user_id);
A good audit query: list every FK column without a covering index.
SELECT c.conrelid::regclass AS table_name,
a.attname AS column_name,
c.conname AS fk_name
FROM pg_constraint c
JOIN pg_attribute a
ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND a.attnum = ANY(i.indkey)
AND i.indkey[0] = a.attnum -- index leads with this column
);
By default, FK checks fire at the end of every statement. For most cases that is what you want. But two scenarios genuinely need deferral:
Mark the constraint DEFERRABLE INITIALLY DEFERRED and the check is postponed until COMMIT:
ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id)
DEFERRABLE INITIALLY DEFERRED;
Now within a transaction the constraint can be transiently violated, as long as the database is consistent again by the time you commit.
ALTER TABLE ... ADD FOREIGN KEY takes a SHARE ROW EXCLUSIVE lock on both tables and validates every existing row before it returns — on a 100M-row table that can mean minutes of downtime. The safe pattern is two-phase: add the constraint as NOT VALID (skip the validation step, lock briefly), then validate it later in a separate, lock-light command.
-- Phase 1: add the constraint immediately, no row scan, brief lock.
ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;
-- Phase 2: validate existing rows. Only takes a SHARE UPDATE EXCLUSIVE lock
-- on the child table — concurrent reads and writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;
After Phase 1 the constraint is enforced for all new writes but the historical data has not been verified. Phase 2 verifies everything; once it succeeds, the constraint is fully valid and indistinguishable from one created with both steps fused.
If Phase 2 reports a violation, the constraint stays NOT VALID; clean up the offending rows and retry. This pattern is standard in zero-downtime migration playbooks (gh-ost, pg-osc, Drizzle's manual migrations, etc.).
Foreign keys translate cleanly into Drizzle's TypeScript schema DSL. The cascade behavior is explicit and visible at the call site, which is where you want it.
import { pgTable, uuid, text, numeric, timestamp } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: uuid("id").primaryKey().defaultRandom(),
email: text("email").notNull().unique(),
});
export const orders = pgTable("orders", {
id: uuid("id").primaryKey().defaultRandom(),
userId: uuid("user_id")
.notNull()
.references(() => users.id, { onDelete: "cascade" }),
total: numeric("total", { precision: 10, scale: 2 }).notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
});
A transaction that exercises the constraint:
import { eq } from "drizzle-orm";
import { db } from "./db";
import { users, orders } from "./schema";
await db.transaction(async (tx) => {
// Inserting an order for a non-existent user throws here — FK violation.
await tx.insert(orders).values({
userId: "00000000-0000-0000-0000-000000000000",
total: "9.99",
});
});
// Deleting a user cascades to all of their orders in one statement.
await db.delete(users).where(eq(users.id, someUserId));
The onDelete: "cascade" lives in the schema file, version-controlled with the rest of the data model — review-time scrutiny is built in.
Already covered above, but it bears repeating because the symptom is invisible until production: parent deletes get progressively slower as child tables grow. Any new FK should land in the same migration as CREATE INDEX ... ON child_table (fk_col).
ON DELETE CASCADE on a table that has millions of children turns a "delete one user" operation into a bulk delete that rewrites huge swaths of your database, holds locks for minutes, and can blow up replication lag. For high-fanout relationships, prefer ON DELETE RESTRICT plus an explicit, batched application-level cleanup job — or a soft-delete pattern where the parent is marked deleted and a background process tombstones children.
You cannot declare a foreign key across separate databases. In a microservices architecture where each service owns its own database, FK enforcement is impossible at the schema layer — the relationship lives only in your application code or in eventual-consistency reconciliation jobs. Recognize that this is a real loss of integrity, not a non-issue. Mitigations include outbox patterns, periodic referential-integrity audits, and very careful API design around delete semantics.
Many ORMs default to NO ACTION if you don't specify a cascade behavior. That's safe for inserts but means parent deletes throw at runtime, which surprises code that assumed a delete would "just work." Make onDelete an explicit decision in every reference — never a default.
Foreign keys are not free — they cost a lookup on every relevant write, and they constrain how you can shard or replicate data. There are legitimate cases for skipping them:
clicks or events table written at extreme volume usually doesn't benefit from referential checks — you index on the join key for query speed and tolerate the occasional dangling reference.Each of these is a real tradeoff, not a free pass: skipping FKs means the integrity invariant moves into application code or batch jobs, which is strictly more failure-prone than a REFERENCES clause. Default to FKs and justify their absence, not the other way around.