CREATE TABLE, Keys & Constraints

12 min read

CREATE TABLE, Keys & Constraints

Constraints enforce data integrity at the database level. Even if your application has validation logic, always add database constraints as a second line of defence.

sql

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  full_name   text NOT NULL,
  role        text NOT NULL DEFAULT 'member'
                CHECK (role IN ('member', 'admin', 'moderator')),
  is_active   boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT NOW(),
  updated_at  timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title       text NOT NULL,
  slug        text NOT NULL UNIQUE,
  body        text,
  published   boolean NOT NULL DEFAULT false,
  published_at timestamptz,
  created_at  timestamptz NOT NULL DEFAULT NOW()
);

Constraint Types

PRIMARY KEY — uniquely identifies each row; implies NOT NULL and creates a B-tree index automatically.
NOT NULL — rejects NULL values for the column.
UNIQUE — ensures all values in the column (or column combination) are distinct.
CHECK — evaluates a boolean expression; the row is rejected if it returns false.
FOREIGN KEY / REFERENCES — enforces referential integrity between tables.
EXCLUDE — advanced constraint using operator classes; useful for ranges (no overlapping bookings).

SERIAL vs GENERATED ALWAYS AS IDENTITY

serial is syntactic sugar that creates a sequence and sets the column default. It has a subtle issue: you can still manually insert any value, bypassing the sequence. GENERATED ALWAYS AS IDENTITY (SQL standard, Postgres 10+) prevents this by default and is the recommended approach for new tables.

sql

-- Legacy approach (still widely used, avoid in new code)
CREATE TABLE old_style (
  id serial PRIMARY KEY
);

-- Modern SQL-standard approach
CREATE TABLE new_style (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- GENERATED BY DEFAULT allows manual inserts (useful for data migrations)
CREATE TABLE flexible_style (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

For distributed systems or when you need IDs before a database round-trip, consider uuid with gen_random_uuid() as the default. This is what CPCODELAB uses for public-facing resource identifiers.

Adding Constraints After Table Creation

sql

-- Add a constraint to an existing table
ALTER TABLE posts
  ADD CONSTRAINT posts_title_not_empty CHECK (length(title) > 0);

-- Add a foreign key
ALTER TABLE posts
  ADD CONSTRAINT posts_author_fk
  FOREIGN KEY (author_id) REFERENCES users(id);

-- Drop a constraint
ALTER TABLE posts DROP CONSTRAINT posts_title_not_empty;

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises