F-6 — Constraints and Data Integrity

Why Constraints Matter

Imagine a bug in your application that accidentally submits an order with customer_id = 999 — but no customer with ID 999 exists. Without constraints, PostgreSQL happily stores this invalid order. Your data becomes inconsistent: you have orders that reference customers who do not exist.

Or your application has a bug that allows two users to register with the same email address. Now you have duplicate accounts and authentication breaks.

Constraints prevent these problems at the database level — not in your application code, not in your API layer, but at the very last line of defence before data is written to disk.

PRIMARY KEY — Every Row Needs a Unique Identity

A primary key uniquely identifies each row. No two rows can have the same primary key value, and it can never be NULL.

sql
-- Using BIGSERIAL (auto-incrementing, recommended for most tables)
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT      NOT NULL
);

-- Using UUID (when you need globally unique IDs)
CREATE TABLE sessions (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    BIGINT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Composite primary key (rare — when no single column is unique, but a combination is)
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)  -- each product appears once per order
);

When you insert a row that violates the primary key:

sql
INSERT INTO users (id, email) VALUES (1, 'alice@example.com');  -- works
INSERT INTO users (id, email) VALUES (1, 'bob@example.com');    -- fails!
-- ERROR: duplicate key value violates unique constraint "users_pkey"
-- DETAIL: Key (id)=(1) already exists.

BIGSERIAL vs manual ID: when you use BIGSERIAL, PostgreSQL automatically assigns the next available integer. You never specify id in your INSERT — it is generated for you. This is the correct pattern for most tables.

NOT NULL — Mandatory Fields

NOT NULL prevents a column from ever being left empty.

sql
CREATE TABLE products (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT      NOT NULL,   -- required
  price NUMERIC(10,2) NOT NULL, -- required
  sku   TEXT                  -- optional (can be NULL)
);

-- This fails:
INSERT INTO products (name) VALUES ('Keyboard');
-- ERROR: null value in column "price" of relation "products" violates not-null constraint

-- This works:
INSERT INTO products (name, price) VALUES ('Keyboard', 129.99);
-- sku is left as NULL — that is fine

Rule: if a column must always have a value, add NOT NULL. If the value is sometimes unknown or optional, leave the column nullable. Be intentional — a column without NOT NULL silently accepts missing data.

UNIQUE — No Duplicates Allowed

UNIQUE ensures no two rows have the same value in that column (or combination of columns).

sql
-- Single column unique
CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT      NOT NULL UNIQUE  -- no two users can have the same email
);

-- Unique constraint on multiple columns (combination must be unique)
CREATE TABLE team_memberships (
  user_id BIGINT NOT NULL,
  team_id BIGINT NOT NULL,
  UNIQUE (user_id, team_id)  -- a user can only be in the same team once
);

When a unique constraint is violated:

sql
INSERT INTO users (email) VALUES ('alice@example.com');  -- works
INSERT INTO users (email) VALUES ('alice@example.com');  -- fails!
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(alice@example.com) already exists.

UNIQUE vs PRIMARY KEY:

• PRIMARY KEY = unique + not null + the table's main identifier (one per table)
• UNIQUE = no duplicates, but NULLs are allowed (and multiple NULLs count as different values in PostgreSQL)

FOREIGN KEY — Referential Integrity

A foreign key ensures that a value in one table references a real row in another table. It is the constraint that makes joins meaningful.

sql
CREATE TABLE customers (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT      NOT NULL
);

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    NOT NULL REFERENCES customers(id),
  total       NUMERIC(10,2) NOT NULL
);

-- This fails because customer_id 999 does not exist:
INSERT INTO orders (customer_id, total) VALUES (999, 49.99);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

What happens when you delete a referenced row?

By default, trying to delete a customer who has orders fails:

sql
DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- DETAIL: Key (id)=(1) is still referenced from table "orders".

You control this behaviour with ON DELETE:

sql
-- Option 1: Cascade — delete the customer's orders too
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE CASCADE,
  total       NUMERIC(10,2) NOT NULL
);
-- Deleting a customer also deletes all their orders

-- Option 2: Set NULL — set customer_id to NULL when customer is deleted
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE SET NULL,
  total       NUMERIC(10,2) NOT NULL
);
-- Order is kept but customer_id becomes NULL (only works if column is nullable)

-- Option 3: Restrict (the default) — refuse to delete if referenced rows exist
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    REFERENCES customers(id) ON DELETE RESTRICT,
  total       NUMERIC(10,2) NOT NULL
);

-- Option 4: Set default value
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT    DEFAULT 0 REFERENCES customers(id) ON DELETE SET DEFAULT,
  total       NUMERIC(10,2) NOT NULL
);

Which to use:

• ON DELETE CASCADE — when child rows have no meaning without the parent (order items without an order)
• ON DELETE SET NULL — when the relationship is optional and child rows are still useful alone (posts by a deleted user still exist, author is just unknown)
• ON DELETE RESTRICT (default) — when you want explicit control and prefer to handle deletion in your application

CHECK — Custom Business Rules

CHECK enforces any condition you can express as a boolean SQL expression:

sql
CREATE TABLE products (
  id       BIGSERIAL PRIMARY KEY,
  name     TEXT      NOT NULL,
  price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  discount NUMERIC(5,2) CHECK (discount BETWEEN 0 AND 100),
  status   TEXT      NOT NULL CHECK (status IN ('active', 'inactive', 'archived'))
);

-- This fails:
INSERT INTO products (name, price, status) VALUES ('Keyboard', -10, 'active');
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
-- DETAIL: Failing row contains (1, Keyboard, -10.00, null, active).

-- Multi-column check (defined at table level, not column level)
CREATE TABLE bookings (
  id         BIGSERIAL PRIMARY KEY,
  start_date DATE NOT NULL,
  end_date   DATE NOT NULL,
  CHECK (end_date > start_date)  -- end must be after start
);

DEFAULT — Automatic Values

DEFAULT specifies the value to use when a column is omitted from an INSERT:

sql
CREATE TABLE posts (
  id         BIGSERIAL   PRIMARY KEY,
  title      TEXT        NOT NULL,
  published  BOOLEAN     NOT NULL DEFAULT false,
  view_count INTEGER     NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- All defaults apply:
INSERT INTO posts (title) VALUES ('My First Post');

-- Result:
-- id=1, title='My First Post', published=false, view_count=0,
-- created_at=NOW(), updated_at=NOW()

Adding Constraints to Existing Tables

You will often need to add constraints after a table already exists — because you are adding a rule to a live system:

sql
-- Add a NOT NULL constraint (requires all existing rows to already be non-null)
ALTER TABLE products ALTER COLUMN price SET NOT NULL;

-- Add a UNIQUE constraint
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Add a FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;

-- Add a CHECK constraint
ALTER TABLE products
ADD CONSTRAINT products_price_positive CHECK (price >= 0);

-- Drop a constraint by name
ALTER TABLE products DROP CONSTRAINT products_price_positive;

Viewing constraint names

PostgreSQL auto-generates constraint names if you don't provide one. Find them with:

sql
\d products    -- shows constraints in psql

-- Or query the catalog:
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'products'::regclass;

Practical Exercise: A Constrained Blog Schema

Build this schema from scratch, applying appropriate constraints at each step:

sql
CREATE TABLE authors (
  id         BIGSERIAL    PRIMARY KEY,
  username   TEXT         NOT NULL UNIQUE,
  email      TEXT         NOT NULL UNIQUE,
  bio        TEXT,                          -- optional
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
  id          BIGSERIAL    PRIMARY KEY,
  author_id   BIGINT       NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
  title       TEXT         NOT NULL,
  slug        TEXT         NOT NULL UNIQUE,  -- URL-friendly version of title
  content     TEXT         NOT NULL,
  status      TEXT         NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft', 'published', 'archived')),
  published_at TIMESTAMPTZ,                  -- NULL until published
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE comments (
  id         BIGSERIAL    PRIMARY KEY,
  post_id    BIGINT       NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id  BIGINT       REFERENCES authors(id) ON DELETE SET NULL,
  -- author_id can be NULL for anonymous comments
  content    TEXT         NOT NULL CHECK (LENGTH(content) >= 1),
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

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)  -- a post can have each tag only once
);

Test your constraints:

sql
-- Insert an author
INSERT INTO authors (username, email) VALUES ('alice', 'alice@example.com');

-- Insert a post
INSERT INTO posts (author_id, title, slug, content)
VALUES (1, 'Hello World', 'hello-world', 'My first post!');

-- Try to insert a post with an invalid status:
INSERT INTO posts (author_id, title, slug, content, status)
VALUES (1, 'Draft Post', 'draft-post', 'Content...', 'pending');
-- ERROR: violates check constraint "posts_status_check"

-- Try to insert a duplicate slug:
INSERT INTO posts (author_id, title, slug, content)
VALUES (1, 'Another Post', 'hello-world', 'Different content');
-- ERROR: violates unique constraint "posts_slug_key"

-- Delete the author — their post is deleted too (CASCADE)
DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;  -- empty — cascaded delete removed it

Summary

The philosophy: the database is the last line of defence. Enforce every rule you can at the database level — it is faster, safer, and catches bugs that application-level validation misses.

Module F-7 completes Phase 1 — building your first real application schema, connecting from application code, and the basic tools every engineer needs before moving to Phase 2.

Next: F-7 — Your First Real Application Schema →