Foreign Key

A foreign key establishes a link between two tables by referencing the primary key or a unique constraint of a parent table. The table containing the foreign key is called the child table; the referenced table is the parent table. Foreign keys prevent invalid data from being inserted and can automatically maintain consistency when parent rows are updated or deleted.

Foreign key syntax

[CONSTRAINT fk_name]
FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_key_columns)
[ON DELETE delete_action]
[ON UPDATE update_action]

PostgreSQL supports five delete/update actions: NO ACTION (default), RESTRICT, CASCADE, SET NULL, and SET DEFAULT.

NO ACTION — the default

Without an explicit action, deleting a parent row that is still referenced by a child row raises an error:

CREATE TABLE customers (
  customer_id INT GENERATED ALWAYS AS IDENTITY,
  customer_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (customer_id)
);

CREATE TABLE contacts (
  contact_id   INT GENERATED ALWAYS AS IDENTITY,
  customer_id  INT,
  contact_name VARCHAR(255) NOT NULL,
  phone        VARCHAR(15),
  email        VARCHAR(100),
  PRIMARY KEY (contact_id),
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

DELETE FROM customers WHERE customer_id = 1;
-- ERROR:  update or delete on table "customers" violates foreign key constraint
-- "fk_customer" on table "contacts"

ON DELETE SET NULL

When the parent row is deleted, the foreign key column in child rows is set to NULL:

CONSTRAINT fk_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE SET NULL

After deleting customer id 1, the contacts for that customer show NULL in customer_id.

ON DELETE CASCADE

The most commonly used action — child rows are automatically deleted when the parent row is deleted:

CONSTRAINT fk_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE CASCADE

DELETE FROM customers WHERE customer_id = 1;
-- The matching rows in contacts are deleted automatically.

SELECT * FROM contacts;
--  contact_id | customer_id | contact_name |     phone      |          email
-- ------------+-------------+--------------+----------------+--------------------------
--           3 |           2 | David Wright | (408)-222-1234 | [email protected]
-- (1 row)

Adding a foreign key to an existing table

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (fk_columns)
REFERENCES parent_table (parent_key_columns)
ON DELETE CASCADE;

To change an existing foreign key action, drop the old constraint and add a new one:

ALTER TABLE contacts DROP CONSTRAINT fk_customer;

ALTER TABLE contacts
ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  ON DELETE CASCADE;

Production tips

• Always index the foreign key column in the child table — PostgreSQL does not create this index automatically, and unindexed foreign keys cause slow lookups on joins and cascades.
• Use ON DELETE CASCADE when child records have no meaning without the parent (e.g., order items without an order). Use ON DELETE SET NULL when child records should be preserved but unlinked.
• Adding a foreign key to an existing table validates all current rows against the parent table — ensure data is clean before running the migration.
• Name foreign key constraints explicitly with the CONSTRAINT clause to make error messages and migrations self-documenting.