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

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.