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
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
UPDATEthat sets the column to a value another row already has.
How to fix it
-
Find the existing row.
SELECT * FROM users WHERE email = '[email protected]';
-
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; -
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
SQLITE_CONSTRAINT_PRIMARYKEYPRIMARY KEY constraint failedSQLITE_CONSTRAINTconstraint failed23505PostgreSQL: duplicate key violates unique constraint1062MySQL: Duplicate entry for key2627SQL Server: Violation of PRIMARY KEY or UNIQUE KEY2601SQL Server: Cannot insert duplicate key rowORA-00001Oracle: unique constraint violated
More extended result codes errors
SQLITE_ERROR_MISSING_COLLSEQno such collation sequenceSQLITE_BUSY_RECOVERYanother process is recovering the WALSQLITE_LOCKED_SHAREDCACHErecord locked in shared cacheSQLITE_CONSTRAINT_CHECKCHECK constraint failedSQLITE_ABORT_ROLLBACKstatement aborted by ROLLBACKSQLITE_BUSY_SNAPSHOTstale read can't become a writeSQLITE_IOERR_SHORT_READread returned fewer bytesSQLITE_CANTOPEN_ISDIRthe path is a directory
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.
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.