Unique Constraints

UNIQUE constraints enforce that key values do not repeat.

This is essential for fields like email addresses, usernames, and natural keys.

Core Concepts

flowchart LR
  A[Incoming value] --> B[Check existing unique index]
  B -->|No duplicate| C[Accept row]
  B -->|Duplicate found| D[Reject change]

Unique style	Example	Use case
Single-column unique	email TEXT UNIQUE	One unique value per row
Composite unique	UNIQUE(first_name, last_name, birth_date)	Unique combinations

Single Column

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  email   TEXT UNIQUE,
  name    TEXT NOT NULL
);

Expected output
Table created with one unique column (email).

Composite Unique

CREATE TABLE room_bookings (
  booking_id   INTEGER PRIMARY KEY,
  room_id      INTEGER NOT NULL,
  booking_date TEXT    NOT NULL,
  slot         TEXT    NOT NULL,
  UNIQUE (room_id, booking_date, slot)
);

Expected output
Table created with unique combination on room/date/slot.

Code Examples

-- Show indexes created for unique enforcement.
PRAGMA index_list(users);

seq	name	unique	origin	partial
0	sqlite_autoindex_users_1	1	u	0

SQLite-Specific Nuances

SQLite Nuance

SQLite allows multiple NULL values in a UNIQUE column because NULL is treated as distinct for uniqueness checks.

Common Pitfalls / Best Practices

Pitfall

Assuming UNIQUE on email also prevents NULL values.

Use NOT NULL with UNIQUE when every row must have a value.

Best Practice

Combine NOT NULL + UNIQUE for business identifiers like email, username, and external IDs.

Quick Challenge

Create an accounts table where username is required and unique.

View Solution

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  username   TEXT NOT NULL UNIQUE
);

Expected output
Table created with strict unique username rule.