Foreign Key

A foreign key is an attribute (or set of attributes) in one table that references the Primary Key of another table. It enforces Referential Integrity — ensuring that relationships between tables remain consistent. A foreign key value must either match an existing primary key value in the referenced table or be NULL (if the column allows it).

Syntax

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    product_id INT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL
);

Naming convention from Database Constraints: use <table_name>_id format (e.g., customer_id) so the reader immediately knows which table is referenced.

Referential Actions

What happens when the referenced Primary Key row is deleted or updated. See Database Constraints for details:

Action	ON DELETE	ON UPDATE
RESTRICT (default)	Block the delete	Block the update
CASCADE	Delete child rows	Update child FK values
SET NULL	Set FK to NULL	Set FK to NULL
SET DEFAULT	Set FK to default value	Set FK to default value

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

Foreign Keys and Performance

• Foreign key columns should always be indexed — without an Index, cascading deletes and joins require full table scans
• PostgreSQL does not auto-index foreign keys (unlike primary keys). Always create the index manually:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);