SQLite UNIQUE Constraints: Single Columns, Composite Keys, and NULLs

UNIQUE Means "No Duplicates Allowed"

A UNIQUE constraint tells SQLite that values in a column (or a group of columns) must not repeat across rows. It's how you say "two users can't share an email" or "a product code appears at most once."

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

The third insert fails with UNIQUE constraint failed: users.email. SQLite checks the constraint on every write and rejects anything that would create a duplicate. The first two rows are saved; the third never lands.

Behind the scenes, UNIQUE is implemented as a unique index — same data structure SQLite uses for fast lookups — so the check is cheap and the column is automatically indexed.

Column-Level vs Table-Level Syntax

You can write UNIQUE two ways. Inline next to a column, or as a separate clause at the end of the table definition:

-- Column-level: simple, one column.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Table-level: same effect, different placement.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

For a single column, both are equivalent — pick whichever reads better. The table-level form becomes essential the moment you need uniqueness across more than one column.

Composite UNIQUE: Multiple Columns Together

Sometimes a single column isn't unique on its own, but a combination should be. A user can enroll in many courses, and a course can have many users — but the same (user_id, course_id) pair shouldn't appear twice:

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- fine, different course
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- fine, different user
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- fails: pair already exists

SELECT * FROM enrollments;

The constraint is on the pair, not on either column alone. User 1 can enroll in many courses, course 100 can have many users — but only once per combination.

This is the bread-and-butter pattern for join tables in many-to-many relationships.

UNIQUE vs PRIMARY KEY

They sound similar and they're related, but they're not the same thing:

• A table has at most one PRIMARY KEY. It can have many UNIQUE constraints.
• PRIMARY KEY is the row's identity — what foreign keys point at, what rowid aliases.
• UNIQUE is just "this value (or combination) doesn't repeat."
• In a regular table, a UNIQUE column can hold NULL values; a PRIMARY KEY cannot (with one historic exception we'll skip).

A common shape:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- internal identity, never changes
    email    TEXT NOT NULL UNIQUE,  -- user-facing identifier, must be unique
    username TEXT NOT NULL UNIQUE   -- another unique handle
);

id is what the rest of the database references. email and username are unique because the application requires it, not because they're identity. If a user changes their email, the id stays the same — that's the point of separating them.

The NULL Quirk

This trips up almost everyone the first time. A UNIQUE column in SQLite accepts as many NULL values as you want:

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- this one fails

SELECT * FROM invites;

Three NULLs, no problem. Two 'ada@example.com', that's a conflict.

The reason: SQL treats NULL as "unknown," and two unknowns aren't considered equal — so the uniqueness check can't say they're duplicates. If you need at most one NULL, the cleanest fix is NOT NULL UNIQUE. If NULLs are valid but only one per other-column combination, reach for a partial index (covered later in the indexes chapter).

Handling Conflicts: ON CONFLICT

By default, a UNIQUE violation aborts the statement. But sometimes you want different behavior — replace the existing row, ignore the new one, or update specific columns. SQLite gives you two ways to ask for that.

The first is built into the constraint with ON CONFLICT:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- replaces the first
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

The second time theme is inserted, the existing row is deleted and the new one takes its place. Other options are IGNORE (silently skip), ABORT (the default), FAIL, and ROLLBACK.

The second way is per-statement, with the upsert syntax — usually more flexible because it can update specific columns:

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

The first insert creates the row. The next two hit the UNIQUE constraint and fall through to the DO UPDATE branch, incrementing count. This is the INSERT ... ON CONFLICT upsert pattern — there's a dedicated page on it later.

UNIQUE Constraint vs UNIQUE Index

CREATE UNIQUE INDEX does the same job as a UNIQUE constraint. In fact, a UNIQUE constraint creates a unique index behind the scenes — they're nearly the same mechanism wearing different hats.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- fails

When to prefer which:

• Constraint when uniqueness is part of the table's definition. It's documented right next to the columns.
• Unique index when you want a partial index (WHERE clause), need a specific name, or want to add it to an existing table without rewriting it. SQLite's ALTER TABLE can't add a constraint, but it can always have an index added.

The behavior on writes is identical. The choice is mostly about where you want the rule to live in the schema.

Adding UNIQUE to an Existing Table

SQLite's ALTER TABLE is intentionally limited — there's no ALTER TABLE ... ADD CONSTRAINT. The two practical options:

-- Option 1: add a unique index. Works on existing tables, same enforcement.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- now fails

Option 2 — when you really want a UNIQUE clause baked into the table definition — is the table rewrite dance: create a new table with the constraint, copy the data over, drop the old one, rename. It's covered in the next page.

A heads-up: if you're adding uniqueness to a column that already has duplicates, the CREATE UNIQUE INDEX will fail. Clean up the duplicate rows first, then add the index.

When UNIQUE Fails: Reading the Error

The error message tells you exactly which constraint blew up:

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

The first form is a single-column constraint on users.email. The second is a composite — both columns are listed because the combination already exists. When you see this:

1. Identify which row already has the conflicting value (SELECT ... WHERE email = '...').
2. Decide whether you want to update that row, skip the insert, or use a different value.
3. If duplicates are expected and you want to merge them, switch to INSERT ... ON CONFLICT DO UPDATE.

The error is loud because most of the time you actually do want to know — silent duplicates would be worse than a failed write.

Next: Dropping and Altering Tables

UNIQUE constraints can't be added to an existing table with a simple ALTER TABLE. That limitation is why SQLite has a particular dance for schema changes — the table rewrite — and it's the topic of the next page, alongside the basics of dropping tables cleanly.