Unique Index

A unique index guarantees no duplicate key values across indexed rows.

It is useful for business identifiers like username, email, and external IDs.

Core Concepts

flowchart LR
  A[Incoming key value] --> B{Already exists in unique index?}
  B -->|Yes| C[Reject write]
  B -->|No| D[Accept write]

Mechanism	Typical use
UNIQUE constraint in table definition	Core schema rules
Explicit CREATE UNIQUE INDEX	Add uniqueness after table exists

UNIQUE Constraint

CREATE TABLE users_uq (
  user_id   INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  full_name TEXT NOT NULL
);

Expected output
Table created with built-in uniqueness rule on email.

UNIQUE Index

CREATE TABLE users_uq_index (
  user_id   INTEGER PRIMARY KEY,
  email     TEXT NOT NULL,
  full_name TEXT NOT NULL
);

CREATE UNIQUE INDEX uq_users_email
ON users_uq_index(email);

Expected output
Unique index created and duplicate emails blocked.

Code Examples

-- Setup explicit unique index example.
INSERT INTO users_uq_index (email, full_name)
VALUES ('ada@example.com', 'Ada');

Expected output
First insert succeeds.

-- Duplicate key attempt (should fail).
INSERT INTO users_uq_index (email, full_name)
VALUES ('ada@example.com', 'Ada Duplicate');

Expected output
Error: UNIQUE constraint failed.

-- Inspect index metadata.
PRAGMA index_list(users_uq_index);

seq	name	unique	origin	partial
0	uq_users_email	1	c	0

SQLite-Specific Nuances

SQLite Nuance

SQLite unique indexes allow multiple NULL values unless the column is also NOT NULL.

Common Pitfalls / Best Practices

Pitfall

Assuming uniqueness implies requiredness.

UNIQUE alone does not prevent NULL unless NOT NULL is also defined.

Best Practice

For required business identifiers, combine NOT NULL and unique enforcement.

Quick Challenge

Add a unique index on users_uq_index(full_name) named uq_users_full_name.

View Solution

CREATE UNIQUE INDEX uq_users_full_name
ON users_uq_index(full_name);

Expected output
Duplicate full names are now rejected by index enforcement.