Primary and Foreign Keys
Entity relationships, ON DELETE CASCADE/SET NULL, and referential integrity
Skills taught: pg.primary-keys, pg.foreign-keys, pg.referential-integrity, pg.on-delete
Primary keys
-- Single column primary key (most common)
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
-- UUID primary key
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
-- Composite primary key (many-to-many junction table)
CREATE TABLE post_tags (
post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
Foreign keys and ON DELETE behavior
CREATE TABLE posts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- ON DELETE CASCADE: when the user is deleted, all their posts are deleted
title TEXT NOT NULL
);
CREATE TABLE comments (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
-- ON DELETE SET NULL: if the commenting user is deleted, keep the comment but null out user_id
body TEXT NOT NULL
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
-- ON DELETE RESTRICT: prevent deleting a customer who has orders
total NUMERIC(10,2)
);
-- Always index foreign key columns for join performance
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
Tip: Always create indexes on foreign key columns. PostgreSQL does NOT automatically index foreign keys. Without an index, DELETE operations on the parent table are slow (sequential scan of child table) and JOINs are slow on large tables.
Frequently Asked Questions
Should I always use foreign keys?
For OLTP apps (web apps, APIs, business software): yes, always. Foreign keys prevent an entire category of data corruption bugs that are expensive to fix after the fact. For analytics/data warehouse tables with billions of rows or high-volume logging, foreign key checks add overhead — teams sometimes skip them there and use application-level consistency checks instead.