Foreign Keys

A foreign key is a column, or set of columns, in one table that references the primary key (or another unique constraint) of another table. It is how a relational database expresses that two tables are related — an order belongs to a customer, a comment belongs to a post — and, critically, it is enforced automatically. PostgreSQL will not let you insert an order for a customer_id that does not exist, and by default will not let you delete a customer who still has orders pointing at them.

Declaring a foreign key

orders referencing customers

SQL

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  total_amount NUMERIC(10, 2) NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO customers (name) VALUES ('Jordan Blake');   -- customer_id = 1
INSERT INTO orders (customer_id, total_amount) VALUES (1, 59.99);  -- OK

INSERT INTO orders (customer_id, total_amount) VALUES (999, 12.00); -- fails

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

This is referential integrity: the database itself guarantees that every customer_id stored in orders corresponds to a real row in customers. No application code has to remember to check that — it is not possible to violate it, by accident or otherwise, as long as the constraint is in place.

What happens on DELETE or UPDATE of the referenced row

By default, PostgreSQL refuses to delete a customers row that still has matching orders. That default can be overridden per foreign key with an ON DELETE (and separately, ON UPDATE) clause describing what should happen to the referencing rows instead.

Action	Behavior on delete of the referenced row
RESTRICT	Refuse the delete if any referencing rows exist. Checked immediately.
NO ACTION	Refuse the delete if any referencing rows exist. This is the default when no action is specified.
CASCADE	Delete the referencing rows too (e.g. deleting a customer deletes their orders).
SET NULL	Set the referencing column to NULL on the referencing rows.
SET DEFAULT	Set the referencing column to its declared DEFAULT value.

Choosing a delete behavior

SQL

-- Deleting a customer deletes their order history too
CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  customer_id  INTEGER REFERENCES customers (customer_id) ON DELETE CASCADE,
  total_amount NUMERIC(10, 2) NOT NULL
);

-- Deleting a customer just detaches their orders instead of destroying them
CREATE TABLE support_tickets (
  ticket_id   SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (customer_id) ON DELETE SET NULL,
  subject     TEXT NOT NULL
);

RESTRICT vs. NO ACTION — a genuine PostgreSQL-relevant nuance

RESTRICT and NO ACTION sound identical, and in the simplest case they behave identically: both block a delete when referencing rows exist. The difference shows up with deferrable constraints. NO ACTION can be declared DEFERRABLE, meaning the check can be postponed until the end of the transaction — useful when a transaction temporarily creates a dangling reference partway through but resolves it before COMMIT. RESTRICT cannot be deferred at all; it is checked immediately, every time. If you ever need a foreign key check deferred to transaction end, it must be NO ACTION, not RESTRICT.

Worked example: orders referencing customers

Putting it together with the sample schema: a customer can have many orders, so the foreign key lives on the "many" side (orders.customer_id), pointing back at the "one" side (customers.customer_id). Trying to violate the relationship in either direction is blocked by the database itself.

Referential integrity in both directions

SQL

-- Blocked: no customer 42 exists
INSERT INTO orders (customer_id, total_amount) VALUES (42, 10.00);

-- Blocked by default: customer 1 still has orders
DELETE FROM customers WHERE customer_id = 1;

-- Allowed: remove the orders first, then the customer
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM customers WHERE customer_id = 1;

ON DELETE CASCADE is easy to reach for and dangerous to reach for carelessly

CASCADE is convenient, but it means a single DELETE on a parent row can silently ripple through several related tables. Before adding it, make sure that destroying the child rows is genuinely the desired behavior — for data you'd rather keep around in a detached state (support tickets, audit logs), SET NULL or SET DEFAULT are usually the safer choice.

A foreign key ties a column to another table's primary key (or unique constraint) and PostgreSQL enforces it automatically.
ON DELETE / ON UPDATE control what happens to referencing rows: RESTRICT, NO ACTION, CASCADE, SET NULL, SET DEFAULT.
RESTRICT is always checked immediately; NO ACTION behaves the same by default but can be deferred to end-of-transaction.
The foreign key column lives on the "many" side of a one-to-many relationship.