PostgreSQL Foreign Keys Quick Reference

Photo by Conny Schneider on Unsplash

1. Basic Foreign Key (Most Common)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id)
);

2. Add to Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

3. Named Constraint (Recommended)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

4. With Actions

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);

Common Actions

• ON DELETE CASCADE - Delete child when parent deleted
• ON DELETE SET NULL - Set FK to NULL when parent deleted
• ON DELETE RESTRICT - Prevent parent deletion (default)

Drop Constraint

ALTER TABLE orders DROP CONSTRAINT fk_customer;

View Constraints

\d table_name