PostgreSQL Foreign Keys Quick Reference

These examples use PostgreSQL syntax. Some details — such as SERIAL, \d, and certain constraint behaviors — may differ in other databases like MySQL or SQLite.

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