0Pricing
SQL Academy · Lesson

UNIQUE Constraints and Composite Keys

Enforce uniqueness on single or multiple columns, understand NULLs in unique indexes, and use composite uniqueness for natural keys.

UNIQUE Constraints and Composite Keys is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

UNIQUE: No Duplicates Allowed

A UNIQUE constraint forbids two rows from sharing the same value in the constrained columns:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) UNIQUE
);

UNIQUE Creates an Index

A UNIQUE constraint is implemented with a unique B-tree index, so lookups by that column are fast.

-- This shows the implicit index:
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

Composite UNIQUE

Uniqueness across a combination of columns:

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  role TEXT NOT NULL,
  UNIQUE (user_id, group_id)        -- a user can be in a group at most once
);

UNIQUE vs PRIMARY KEY

A primary key is essentially UNIQUE + NOT NULL + designated identity. A table can have ONE primary key but many UNIQUE constraints.

NULL in UNIQUE

Standard SQL treats NULL as distinct from every other value (including other NULLs). You can have multiple NULL rows in a UNIQUE column:

CREATE TABLE invites (email VARCHAR(255) UNIQUE);
INSERT INTO invites VALUES (NULL), (NULL);   -- both succeed

-- PostgreSQL 15+ offers UNIQUE NULLS NOT DISTINCT to treat NULL as equal:
CREATE TABLE invites2 (email VARCHAR(255) UNIQUE NULLS NOT DISTINCT);

Composite Primary Key

If a natural composite key fits, use it as the PRIMARY KEY:

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Surrogate Key + UNIQUE Natural Key

A common pattern: synthetic id as primary key, real-world uniqueness via UNIQUE:

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name TEXT NOT NULL
);

Partial UNIQUE Indexes

Uniqueness only among some rows:

-- One active email per user (deleted users can re-register):
CREATE UNIQUE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;

Expression UNIQUE Indexes

Uniqueness on a computed expression — useful for case-insensitive emails:

CREATE UNIQUE INDEX users_email_ci_idx ON users (LOWER(email));

UNIQUE Violation Errors

A duplicate insert returns:

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(alice@example.com) already exists.

UPSERT Builds on UNIQUE

ON CONFLICT needs a UNIQUE or PRIMARY KEY to target — that's how PostgreSQL knows what counts as a duplicate.

Recap

UNIQUE is your second-most-important constraint after NOT NULL.

  • Single or composite
  • Partial UNIQUE for "only among active rows"
  • Expression UNIQUE for case-insensitive keys
  • Watch the NULL-is-distinct rule

Quick Check

Can a UNIQUE column contain multiple NULL values in standard SQL?

Frequently asked questions

Is the “UNIQUE Constraints and Composite Keys” lesson free?

Yes — the full text of “UNIQUE Constraints and Composite Keys” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “UNIQUE Constraints and Composite Keys”?

Enforce uniqueness on single or multiple columns, understand NULLs in unique indexes, and use composite uniqueness for natural keys. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “UNIQUE Constraints and Composite Keys” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. NOT NULL and CHECK Constraints
  2. UNIQUE Constraints and Composite Keys
  3. FOREIGN KEY and Referential Actions
  4. DEFAULT Values and Generated Columns
← Back to SQL Academy