Primary Keys, Foreign Keys and Table Relationships

Primary keys and foreign keys are the heart of relational database design. The primary key uniquely identifies every row in a table — it is the address by which the database finds, updates, and deletes specific records. Foreign keys connect tables, allowing you to model the real-world relationships between entities: a post belongs to a user, a comment belongs to a post, a product belongs to a category. Understanding how to choose primary key types and how foreign key constraints behave — especially during deletions — is essential for designing a schema that is both correct and performant for your FastAPI application.

Primary Key Types #

-- ── Option 1: BIGSERIAL — auto-incrementing integer (most common) ─────────────
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,   -- 1, 2, 3, 4 ...
    title TEXT NOT NULL
);
-- Pros: small (8 bytes), fast for joins, easy to debug
-- Cons: sequential → predictable (enumerate IDs), reveals volume

-- ── Option 2: UUID — universally unique (recommended for public-facing APIs) ──
CREATE EXTENSION IF NOT EXISTS "pgcrypto";   -- enable UUID generation

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL
);
-- Inserts a random UUID automatically if not provided:
-- "a8098c1a-f86e-11da-bd1a-00112444be1e"

-- Pros: non-sequential (not guessable), works across distributed systems
-- Cons: 16 bytes (vs 8 for BIGINT), slightly slower for large index scans

-- ── Option 3: Composite primary key — two columns together are unique ─────────
CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)   -- no separate id column needed
);

Foreign Keys and Referential Integrity #

-- ── Basic foreign key ─────────────────────────────────────────────────────────
CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    author_id BIGINT    NOT NULL REFERENCES users(id),
    title     TEXT      NOT NULL
);
-- REFERENCES users(id) means:
-- - author_id must be a value that exists in users.id
-- - You cannot insert a post with author_id=999 if no user has id=999
-- - Default ON DELETE: RESTRICT — cannot delete user if they have posts

-- ── Explicit ON DELETE behaviour ──────────────────────────────────────────────
-- CASCADE:    delete child rows when parent is deleted
-- RESTRICT:   prevent parent deletion if children exist (default)
-- SET NULL:   set FK column to NULL when parent deleted (column must allow NULL)
-- SET DEFAULT: set FK to its default value when parent deleted
-- NO ACTION:  like RESTRICT but deferred — checked at end of transaction

CREATE TABLE posts (
    id        BIGSERIAL  PRIMARY KEY,
    author_id BIGINT     NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category_id BIGINT   REFERENCES categories(id) ON DELETE SET NULL
    -- author_id: delete post when user deleted (post is owned by user)
    -- category_id: keep post but null the category when category deleted
);

-- ── Verify FK constraints are working ────────────────────────────────────────
INSERT INTO posts (title, author_id) VALUES ('Test', 9999);
-- ERROR: insert or update on table "posts" violates foreign key constraint
-- DETAIL: Key (author_id)=(9999) is not present in table "users".

Modelling Relationship Types #

-- ── One-to-Many: user has many posts ─────────────────────────────────────────
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE posts (
    id        BIGSERIAL PRIMARY KEY,
    author_id BIGINT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title     TEXT      NOT NULL
);
-- Foreign key goes in the "many" side (posts)

-- ── One-to-One: user has one profile ──────────────────────────────────────────
CREATE TABLE user_profiles (
    user_id  BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    -- user_id is BOTH the PK and the FK — enforces one-to-one
    bio      TEXT,
    avatar_url TEXT
);

-- ── Many-to-Many: posts have many tags, tags belong to many posts ─────────────
CREATE TABLE tags (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT      NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()  -- when was this tag assigned?
);

-- Query: find all tags for post 1
SELECT t.name
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
WHERE pt.post_id = 1;

Common Mistakes #

Mistake 1 — Using CASCADE everywhere without thinking #

❌ Wrong — mass deletion of unintended data:

-- If user is deleted: all posts deleted → all comments deleted → all likes deleted
-- Deleting one user row removes thousands of rows silently!

✅ Better — consider soft delete (deleted_at timestamp) for user-generated content.

Mistake 2 — Nullable foreign key that should be required #

❌ Wrong — post can exist without an author:

author_id BIGINT REFERENCES users(id)   -- NULL allowed by default!

✅ Correct — add NOT NULL if the relationship is required:

author_id BIGINT NOT NULL REFERENCES users(id)   -- ✓

Mistake 3 — Forgetting the EXTENSION for UUID generation #

❌ Wrong — gen_random_uuid() fails without the extension:

id UUID DEFAULT gen_random_uuid()
-- ERROR: function gen_random_uuid() does not exist

✅ Correct — enable pgcrypto first:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- Or use: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; with uuid_generate_v4()

Quick Reference #