Foreign Key in PostgreSQL

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 the child table; the referenced table is the parent table. Foreign keys prevent invalid data from being inserted into the child table and can automatically maintain consistency when parent rows are updated or deleted.

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:

Practical Example

Create an orders table referencing a products table, demonstrating the default NO ACTION behavior:

CREATE TABLE products (
  product_id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  unit_price   NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
  order_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  placed_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE line_items (
  line_item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id     INT NOT NULL,
  product_id   INT NOT NULL,
  quantity     INT NOT NULL DEFAULT 1,
  CONSTRAINT fk_order
    FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT fk_product
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

Attempting to delete a product that has line items fails with NO ACTION:

DELETE FROM products WHERE product_id = 1;
-- ERROR:  update or delete on table "products" violates foreign key constraint
-- "fk_product" on table "line_items"

ON DELETE CASCADE, SET NULL, and ON UPDATE CASCADE

Rebuild fk_product as ON DELETE CASCADE so that deleting a product automatically removes its line items:

ALTER TABLE line_items DROP CONSTRAINT fk_product;

ALTER TABLE line_items
  ADD CONSTRAINT fk_product
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE;

Use ON DELETE SET NULL when child rows should survive but lose the reference — useful for soft-delete or archive patterns:

ALTER TABLE line_items DROP CONSTRAINT fk_order;

ALTER TABLE line_items
  ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
    ON DELETE SET NULL;

Propagate primary key changes to child rows with ON UPDATE CASCADE:

ALTER TABLE line_items
  ADD CONSTRAINT fk_product
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

Inspect the resulting constraints:

SELECT
  conname AS constraint_name,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'line_items'::regclass
  AND contype = 'f';

Deferrable Constraints

By default, foreign key checks run at the end of each statement. Declare a constraint DEFERRABLE INITIALLY DEFERRED to postpone the check until the transaction commits — useful when loading data that has circular references or when inserting parent and child rows in the same batch:

ALTER TABLE line_items
  ADD CONSTRAINT fk_order_deferred
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
    DEFERRABLE INITIALLY DEFERRED;

Within a single transaction, you can also override deferral on the fly:

BEGIN;
SET CONSTRAINTS fk_order_deferred IMMEDIATE;
-- ... inserts/updates ...
COMMIT;

Testing with Vela

Foreign key constraint changes — switching from NO ACTION to CASCADE, adding NOT VALID constraints, or bulk-loading data with temporarily deferred constraints — are exactly the kind of operations that are trivial in development but carry real risk in production. Vela’s database branching lets you create a copy-on-write clone of your production database, run the constraint migration on the branch, verify cascade behavior with realistic data volumes, and confirm that your application handles the new behavior correctly — all before touching the live schema.

Production Tips

• Always index the foreign key column in the child table. PostgreSQL does not create this automatically, and an unindexed foreign key column causes full table scans during cascade operations and joins.
• Use NOT VALID when adding a foreign key to a large table: ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES parent(id) NOT VALID; then validate separately: ALTER TABLE child VALIDATE CONSTRAINT fk; — the validation step acquires a weaker lock.
• Name all foreign key constraints explicitly with the CONSTRAINT clause. Auto-generated names are hard to reference in rollback migrations and error messages.
• ON DELETE CASCADE is powerful but irreversible. Test cascade behavior with a representative data set before enabling it on production data.
• For tables with concurrent writes, adding a validated foreign key can cause contention. Schedule the VALIDATE CONSTRAINT step during a low-traffic window.
• To find orphaned child rows before adding a new foreign key, run: SELECT COUNT(*) FROM child WHERE fk_col NOT IN (SELECT id FROM parent) OR fk_col IS NULL;