PostgreSQL — Topic 4: Constraints

⚡

Overview — The 6 Constraint Types

What they are, where they live, what they enforce

▾

At a Glance

All six constraints enforce data integrity at the database level — meaning even if your application has a bug, the database will still reject invalid data.

Constraint	Enforces	Creates Index?	Nullable?
PRIMARY KEY	Unique + Not Null — exactly one per table	Yes (B-Tree)	Never
FOREIGN KEY	Referenced row must exist in parent table	No (add manually)	Yes (NULL = no ref)
UNIQUE	No two rows share the same value(s)	Yes (B-Tree)	Yes (NULLs not equal)
CHECK	Custom boolean expression must be TRUE	No	Depends on expression
NOT NULL	Column must always have a value	No	Never
EXCLUSION	No two rows conflict by a custom operator	Yes (GiST/B-Tree)	Yes

Column-level vs Table-level

Constraints can be defined inline with the column or separately. They behave identically — the difference is syntax and readability.

✅ Column-level (inline)

CREATE TABLE users (
  id    INT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

✅ Table-level (named)

CREATE TABLE users (
  id    INT,
  email TEXT NOT NULL,
  CONSTRAINT pk_users       PRIMARY KEY(id),
  CONSTRAINT uq_users_email UNIQUE(email)
);

💡

Always name your constraints in production. Named constraints give clear error messages like violates constraint "chk_products_price" instead of a cryptic pg error. Convention: pk_table, fk_table_col, uq_table_col, chk_table_col.

4.1

PRIMARY KEY

The unique, non-null identifier for every row

▾

What it Does

A primary key is UNIQUE + NOT NULL combined. Every table should have exactly one. PostgreSQL automatically creates a B-Tree index on the primary key column(s).

-- Single-column PK (most common)
CREATE TABLE users (
  id         BIGINT  GENERATED ALWAYS AS IDENTITY,
  email      TEXT    NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT pk_users PRIMARY KEY (id)
);

-- Shorthand (same result)
CREATE TABLE users (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT   NOT NULL
);

Composite Primary Key

A PK can span multiple columns. Common in many-to-many join tables — the combination must be unique, not the individual columns.

-- Many-to-many: user_roles join table
-- A user can have a role only once
CREATE TABLE user_roles (
  user_id BIGINT NOT NULL,
  role_id INTEGER NOT NULL,
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id)
);

-- ✅ OK — user 1 has role 2, user 1 has role 3
INSERT INTO user_roles(user_id, role_id) VALUES (1, 2);
INSERT INTO user_roles(user_id, role_id) VALUES (1, 3);

-- ❌ FAILS — user 1 already has role 2
INSERT INTO user_roles(user_id, role_id) VALUES (1, 2);

IDENTITY vs SERIAL vs UUID as PK

Method	SQL	Notes
Best IDENTITY	`BIGINT GENERATED ALWAYS AS IDENTITY`	SQL standard, prevents accidental overrides
Legacy SERIAL	`SERIAL` / `BIGSERIAL`	Old PostgreSQL pattern; still works, just older style
Distributed UUID	`UUID DEFAULT gen_random_uuid()`	Use when services generate IDs independently

Adding / Dropping a Primary Key

-- Add a PK after table creation
ALTER TABLE users
  ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- Drop a PK
ALTER TABLE users
  DROP CONSTRAINT pk_users;

-- View constraints on a table
SELECT conname, contype
FROM   pg_constraint
WHERE  conrelid = 'users'::regclass;

4.2

FOREIGN KEY

Referential integrity — links between tables

▾

What it Does

A foreign key ensures the value in the child column always exists in the parent table's referenced column. It prevents orphaned rows — records that point to nothing.

CREATE TABLE users (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT   NOT NULL
);

CREATE TABLE orders (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT      NOT NULL,
  total      NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

-- ✅ OK — user 1 exists
INSERT INTO users(email)   VALUES ('alice@example.com');   -- id = 1
INSERT INTO orders(user_id, total) VALUES (1, 499.00);

-- ❌ FAILS — user 999 does not exist
INSERT INTO orders(user_id, total) VALUES (999, 99.00);

Referential Actions — ON DELETE / ON UPDATE

What should happen to child rows when the parent row is deleted or updated? PostgreSQL offers five actions.

Action	On parent DELETE/UPDATE	Use When
`NO ACTION` (default)	Error — reject if child rows exist	Strict data integrity (most cases)
`RESTRICT`	Error — same as NO ACTION but checked immediately	Same as NO ACTION for most use cases
`CASCADE`	Auto-delete/update all child rows	Orders → OrderItems, Conversations → Messages
`SET NULL`	Set FK column to NULL in child rows	Optional relationships (created_by → user deleted)
`SET DEFAULT`	Set FK column to its DEFAULT value	Rare — requires a sensible default

🎮 Interactive: Choose an ON DELETE action — see what happens

← Pick an action to see its behavior when DELETE FROM users WHERE id = 1 is run and user 1 has 5 orders.

-- CASCADE: delete user → all their orders auto-deleted
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

-- SET NULL: delete user → orders kept, user_id set to NULL
CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  ON DELETE SET NULL

-- E-commerce: cascade order → order_items
CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id)
  REFERENCES orders(id)
  ON DELETE CASCADE

DEFERRABLE Constraints

By default FK checks happen immediately. Sometimes you need to insert rows in a batch where the parent doesn't exist yet — use DEFERRABLE to defer the check to commit time.

CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  DEFERRABLE INITIALLY DEFERRED;  -- checked at COMMIT, not per-statement

-- Can also defer per transaction
BEGIN;
SET CONSTRAINTS fk_orders_user DEFERRED;
INSERT INTO orders(user_id, total) VALUES (100, 50);  -- user 100 doesn't exist yet
INSERT INTO users(id, email) VALUES (100, 'new@example.com');
COMMIT;  -- FK checked here — user 100 now exists ✅

⚠️ Always Index FK Columns

PostgreSQL does NOT auto-create an index on the child FK column. Without an index, deleting a parent row requires a full scan of the child table — catastrophic on large tables.

-- Always add this index for every FK column!
CREATE INDEX idx_orders_user_id  ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

🚨

Critical: A common production outage cause is missing FK indexes. When you delete a user and the orders.user_id column has no index, PostgreSQL does a full sequential scan of the entire orders table for every delete.

4.3

UNIQUE

No duplicate values — single or multi-column

▾

Basic UNIQUE

Ensures no two rows have the same value in the constrained column(s). PostgreSQL auto-creates a B-Tree index for each UNIQUE constraint.

CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT   NOT NULL,
  username   TEXT   NOT NULL,
  phone      TEXT,               -- optional

  CONSTRAINT uq_users_email    UNIQUE (email),
  CONSTRAINT uq_users_username UNIQUE (username)
  -- phone is nullable; multiple NULLs allowed (NULLs are never "equal")
);

-- ✅ OK
INSERT INTO users(email, username) VALUES ('alice@x.com', 'alice');

-- ❌ FAILS: duplicate email
INSERT INTO users(email, username) VALUES ('alice@x.com', 'alice2');

💡

NULLs and UNIQUE: NULL is never equal to NULL in SQL. So a UNIQUE column can have multiple NULL values. This lets you have optional unique fields — e.g. phone can be NULL for many users.

Composite UNIQUE

Enforce uniqueness across a combination of columns. Very common in many-to-many tables and natural business rules.

-- A user can only like a post once
CREATE TABLE post_likes (
  user_id    BIGINT      NOT NULL,
  post_id    BIGINT      NOT NULL,
  liked_at   TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT pk_post_likes  PRIMARY KEY (user_id, post_id),
  CONSTRAINT fk_likes_user  FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_likes_post  FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- In a SaaS: org_id + slug must be unique per organization
CREATE TABLE projects (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  org_id     BIGINT NOT NULL,
  slug       TEXT   NOT NULL,
  CONSTRAINT uq_projects_org_slug UNIQUE (org_id, slug)
);

UNIQUE vs PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
Allows NULL	No	Yes
Per table	Only one	Multiple allowed
Auto index	Yes	Yes
Used as FK target	Yes	Yes

4.4

CHECK

Custom business rules enforced at the database level

▾

Basics

A CHECK constraint runs a boolean expression on every INSERT and UPDATE. If the expression returns FALSE, the operation is rejected. If it returns NULL (e.g. when the value is NULL), the check is passed by default.

CREATE TABLE products (
  id         BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT         NOT NULL,
  price      NUMERIC(12,2) NOT NULL,
  discount   NUMERIC(5,2),
  stock_qty  INTEGER      NOT NULL,
  rating     SMALLINT,

  CONSTRAINT chk_products_price    CHECK (price > 0),
  CONSTRAINT chk_products_discount CHECK (discount IS NULL OR (discount >= 0 AND discount <= 100)),
  CONSTRAINT chk_products_stock    CHECK (stock_qty >= 0),
  CONSTRAINT chk_products_rating   CHECK (rating IS NULL OR rating BETWEEN 1 AND 5)
);

-- ✅ OK
INSERT INTO products(name, price, stock_qty) VALUES ('Phone', 999, 50);

-- ❌ FAILS: price must be > 0
INSERT INTO products(name, price, stock_qty) VALUES ('Phone', -10, 50);

Cross-Column CHECK

CHECK expressions can reference multiple columns — perfect for business rules that span fields.

CREATE TABLE promotions (
  id           BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         TEXT        NOT NULL,
  starts_at    TIMESTAMPTZ NOT NULL,
  ends_at      TIMESTAMPTZ NOT NULL,
  min_order    NUMERIC(12,2),
  max_discount NUMERIC(12,2),

  -- end must be after start
  CONSTRAINT chk_promo_dates
    CHECK (ends_at > starts_at),

  -- if min_order set, it must be positive
  CONSTRAINT chk_promo_min_order
    CHECK (min_order IS NULL OR min_order > 0),

  -- discount must be less than the minimum order value
  CONSTRAINT chk_promo_discount_reasonable
    CHECK (max_discount IS NULL OR min_order IS NULL OR max_discount <= min_order)
);

CHECK for ENUM-like Behavior

When you want controlled values but need flexibility to change them without recreating a type — use TEXT + CHECK.

CREATE TABLE orders (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT   NOT NULL DEFAULT 'pending',
  CONSTRAINT chk_orders_status
    CHECK (status IN (
      'pending', 'confirmed', 'processing',
      'shipped', 'delivered', 'cancelled'
    ))
);

-- To add a new status: just drop and re-add the constraint (no table rewrite)
ALTER TABLE orders DROP CONSTRAINT chk_orders_status;
ALTER TABLE orders ADD CONSTRAINT chk_orders_status
  CHECK (status IN (
    'pending', 'confirmed', 'processing',
    'shipped', 'delivered', 'cancelled', 'refunded'  -- new!
  )) NOT VALID;  -- NOT VALID: skip checking existing rows (fast on big tables)

-- Then validate existing rows separately (no lock)
ALTER TABLE orders VALIDATE CONSTRAINT chk_orders_status;

4.5

NOT NULL

The most common constraint — every required column needs this

▾

Basics

NOT NULL prevents NULL from being inserted into a column. In practice, every required field should be NOT NULL — the more NOT NULL columns you have, the simpler your application logic is (no null checks everywhere).

CREATE TABLE users (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT        NOT NULL,          -- required
  name       TEXT        NOT NULL,          -- required
  phone      TEXT,                          -- optional (nullable)
  avatar_url TEXT,                          -- optional
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()  -- auto-set
);

-- ❌ FAILS: email is NOT NULL
INSERT INTO users(email, name) VALUES (NULL, 'Alice');

Adding NOT NULL to an Existing Column

Adding NOT NULL to a column with existing NULLs will fail. You need to fill the NULLs first.

-- Step 1: fill existing NULLs
UPDATE users SET name = 'Unknown' WHERE name IS NULL;

-- Step 2: add NOT NULL constraint
ALTER TABLE users
  ALTER COLUMN name SET NOT NULL;

-- Remove NOT NULL (make nullable again)
ALTER TABLE users
  ALTER COLUMN name DROP NOT NULL;

NOT NULL + DEFAULT: A Common Pattern

Combine NOT NULL with a DEFAULT so columns are never null but still auto-filled.

CREATE TABLE articles (
  id          BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       TEXT        NOT NULL,
  content     TEXT        NOT NULL DEFAULT '',        -- empty string default
  is_published BOOLEAN   NOT NULL DEFAULT FALSE,     -- starts unpublished
  view_count  INTEGER     NOT NULL DEFAULT 0,         -- starts at 0
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4.6

Exclusion Constraints

No two rows can "conflict" by a custom operator — ranges, rooms, time slots

▾

What Problem Does It Solve?

Imagine a hotel booking system: the same room cannot be booked by two guests during overlapping dates. A UNIQUE constraint won't help here because overlapping ranges can have different values. This is exactly what EXCLUSION constraints solve.

🏨

The rule: "No two rows where room_id is EQUAL AND booking_period OVERLAPS" — this is beyond what UNIQUE can express.

Real-World Example: Room Booking

-- Requires the btree_gist extension
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  id          BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id     INTEGER NOT NULL,
  guest_name  TEXT    NOT NULL,
  -- tstzrange = a timestamptz range type
  period      TSTZRANGE NOT NULL,

  CONSTRAINT no_overlapping_bookings
    EXCLUDE USING gist (
      room_id  WITH =,       -- same room
      period   WITH &&       -- overlapping periods
    )
);

-- ✅ OK: different rooms overlap
INSERT INTO room_bookings(room_id, guest_name, period) VALUES
  (101, 'Alice', '[2025-06-01, 2025-06-05)'::TSTZRANGE),
  (102, 'Bob',   '[2025-06-03, 2025-06-07)'::TSTZRANGE);

-- ✅ OK: same room, no overlap (Alice leaves before Carol arrives)
INSERT INTO room_bookings(room_id, guest_name, period) VALUES
  (101, 'Carol', '[2025-06-06, 2025-06-10)'::TSTZRANGE);

-- ❌ FAILS: room 101 already booked for those dates
INSERT INTO room_bookings(room_id, guest_name, period) VALUES
  (101, 'Dave',  '[2025-06-04, 2025-06-08)'::TSTZRANGE);

More Real-World Use Cases

System	Exclusion Rule	Expression
Hotel booking	Same room, overlapping dates	`room_id = AND period &&`
Doctor scheduling	Same doctor, overlapping appointment slots	`doctor_id = AND slot &&`
Resource allocation	Same server, overlapping maintenance windows	`server_id = AND window &&`
Conference rooms	Same room, overlapping meeting times	`room_id = AND timeslot &&`

Range Types

PostgreSQL has built-in range types that work seamlessly with exclusion constraints.

-- Range type examples
SELECT '[2025-01-01, 2025-12-31]'::DATERANGE;    -- date range (inclusive)
SELECT '[2025-01-01, 2025-12-31)'::DATERANGE;    -- [ = inclusive, ) = exclusive
SELECT '[1, 100]'::INT4RANGE;                    -- integer range

-- && = overlap operator
SELECT '[2025-06-01, 2025-06-10)'::DATERANGE
    && '[2025-06-05, 2025-06-15)'::DATERANGE;   -- → true (overlapping!)

SELECT '[2025-06-01, 2025-06-05)'::DATERANGE
    && '[2025-06-05, 2025-06-10)'::DATERANGE;   -- → false (adjacent, not overlapping)

🧪

Constraint Violation Simulator

Click to see what error each constraint produces

▾

What Error Would You Get?

Click a scenario — see the PostgreSQL error message:

← Click a scenario above to see the error

Complete E-Commerce Table with All Constraints

Here's a real production-grade table using all constraint types together:

CREATE TABLE order_items (
  id           BIGINT        GENERATED ALWAYS AS IDENTITY,
  order_id     BIGINT        NOT NULL,
  product_id   BIGINT        NOT NULL,
  quantity     INTEGER       NOT NULL,
  unit_price   NUMERIC(12,2) NOT NULL,
  discount_pct NUMERIC(5,2)  NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

  -- 4.1 Primary Key
  CONSTRAINT pk_order_items
    PRIMARY KEY (id),

  -- 4.2 Foreign Keys
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,

  -- 4.3 Unique: same product not duplicated in same order
  CONSTRAINT uq_order_items_order_product
    UNIQUE (order_id, product_id),

  -- 4.4 Check constraints
  CONSTRAINT chk_order_items_qty
    CHECK (quantity > 0),
  CONSTRAINT chk_order_items_price
    CHECK (unit_price > 0),
  CONSTRAINT chk_order_items_discount
    CHECK (discount_pct >= 0 AND discount_pct <= 100)

  -- 4.5 NOT NULL already inline on each column above
);

-- Indexes for FK columns (manual)
CREATE INDEX idx_order_items_order   ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

📝

Quick Quiz

Test your constraint knowledge

▾

Constraints

Overview — The 6 Constraint Types

At a Glance

Column-level vs Table-level

PRIMARY KEY

What it Does

Composite Primary Key

IDENTITY vs SERIAL vs UUID as PK

Adding / Dropping a Primary Key

FOREIGN KEY

What it Does

Referential Actions — ON DELETE / ON UPDATE

🎮 Interactive: Choose an ON DELETE action — see what happens

DEFERRABLE Constraints

⚠️ Always Index FK Columns

UNIQUE

Basic UNIQUE

Composite UNIQUE

UNIQUE vs PRIMARY KEY

CHECK

Basics

Cross-Column CHECK

CHECK for ENUM-like Behavior

NOT NULL

Basics

Adding NOT NULL to an Existing Column

NOT NULL + DEFAULT: A Common Pattern

Exclusion Constraints

What Problem Does It Solve?

Real-World Example: Room Booking

More Real-World Use Cases

Range Types

Constraint Violation Simulator

What Error Would You Get?

Click a scenario — see the PostgreSQL error message:

Complete E-Commerce Table with All Constraints

Quick Quiz

Q1: A user_follows table should let user A follow user B only once. Which constraint is most appropriate?

Q2: You delete a user from the users table. You want all their orders to be automatically deleted too. What FK action should you use?

Q3: A hotel booking system needs to prevent two guests from booking the same room for overlapping dates. Which constraint type handles this?

Q4: You add a FK from orders.user_id → users.id. Does PostgreSQL automatically create an index on orders.user_id?

Q1: A `user_follows` table should let user A follow user B only once. Which constraint is most appropriate?

Q2: You delete a user from the `users` table. You want all their orders to be automatically deleted too. What FK action should you use?

Q4: You add a FK from `orders.user_id` → `users.id`. Does PostgreSQL automatically create an index on `orders.user_id`?