// SQLite error reference

SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed

An INSERT or UPDATE would duplicate a value in a UNIQUE column or unique index. The message names the table and column.

Result code
SQLITE_CONSTRAINT_UNIQUE
Name
2067
Category
Extended result codes
Error message
UNIQUE constraint failed: users.email

What it means

SQLite returns SQLITE_CONSTRAINT_UNIQUE (2067) when a write would create two rows with the same value in a UNIQUE constraint or unique index. The message lists the columns, as table.column, comma-separated for a multi-column key. Duplicate primary keys are reported with SQLITE_CONSTRAINT_PRIMARYKEY but use the same message text.

NULLs don't count as duplicates: a unique column can hold any number of NULLs.

Common causes

  • Inserting a row that already exists, for example re-running an import.
  • Two values that differ only in case, in a column declared COLLATE NOCASE.
  • An UPDATE that sets the column to a value another row already has.

How to fix it

  1. Find the existing row.

    SELECT * FROM users WHERE email = '[email protected]';
  2. Update the existing row instead, with an upsert (SQLite 3.24 and later).

    INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
    ON CONFLICT (email) DO UPDATE SET name = excluded.name;
  3. Or skip duplicates on purpose.

    INSERT OR IGNORE INTO users (email, name) VALUES ('[email protected]', 'Ada');

Reproduce it safely

CREATE TABLE users (email TEXT UNIQUE);
INSERT INTO users VALUES ('a@x');
INSERT INTO users VALUES ('a@x');  -- Runtime error: UNIQUE constraint failed: users.email (19)

Questions

What is the difference between INSERT OR REPLACE and an upsert?

INSERT OR REPLACE deletes the conflicting row and inserts a new one, so columns you didn't supply are reset and delete triggers and foreign key actions can fire. An upsert with ON CONFLICT DO UPDATE changes the existing row in place.

Related errors

More extended result codes errors

The code and message text are from the SQLite result and error codes documentation. Browse the full SQLite error list or search every database error.

// fewer errors, faster fixes

Work on SQLite in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQLite and the other databases you use.