SQLite: UNIQUE constraint failed

Result code

SQLITE_CONSTRAINT (19); extended SQLITE_CONSTRAINT_UNIQUE (2067) or SQLITE_CONSTRAINT_PRIMARYKEY (1555)

Product

SQLite library

Versions

SQLite 3.x (examples run on 3.39.2; shell output from 3.50.6)

Category

Constraint Violations

Last reviewed

24 October 2026

What it means

This error means a row would store a value that already exists in a column (or set of columns) that must be unique, so SQLite rejected the statement.

A UNIQUE constraint, a unique index or a PRIMARY KEY allows each value only once. When an INSERT or UPDATE would create a second row with the same value, SQLite stops the statement and reports the table and column that rejected it (customers.email). For a key over several columns, every column of the key is listed.

SQLite uses the same message for PRIMARY KEY conflicts; only the extended result code differs (1555 instead of 2067), and most drivers show just the primary code, 19 (SQLITE_CONSTRAINT). A column declared INTEGER PRIMARY KEY is an alias for the table's rowid: leave it out of the INSERT and SQLite assigns the next free value, which avoids most duplicate-id errors. Other constraint failures share code 19 too, such as NOT NULL constraint failed and FOREIGN KEY constraint failed, so check the message, not only the code.

Common causes

• The same row is inserted twice: a retried request, a double-tapped button or an import or migration script that runs again.
• The application supplies ids itself (for example copied from a server database or another device) and one of them is already taken.
• An UPDATE changes a unique column, such as an email address, to a value another row already has.
• CREATE UNIQUE INDEX is run on a column that already contains duplicate values.

Diagnose it

Read-only These statements only read the schema (sqlite_schema and read-only PRAGMA queries) or your own data. They do not change data, settings or the database file, so they are safe to run first.

PRAGMA index_list(customers);
PRAGMA index_info(sqlite_autoindex_customers_1);

SELECT *
FROM customers
WHERE email = 'sara@example.com';  -- the value you tried to write

SELECT name, COUNT(*) AS copies
FROM customers
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY copies DESC;

SELECT sql
FROM sqlite_schema
WHERE type IN ('table', 'index') AND tbl_name = 'customers';

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO customers (email, name)
VALUES ('sara@example.com', 'Sara Ahmed')
ON CONFLICT (email) DO UPDATE SET name = excluded.name;

INSERT INTO customers (email, name)
VALUES ('sara@example.com', 'Sara'), ('ali@example.com', 'Ali')
ON CONFLICT (email) DO NOTHING;

INSERT INTO customers (email, name) VALUES ('ali@example.com', 'Ali');  -- no id supplied
SELECT last_insert_rowid();

CREATE UNIQUE INDEX ux_customers_name ON customers (name);

Examples

Inserting an email address that is already registered

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

INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara');
INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara B');

UNIQUE constraint failed: customers.email

Supplying a primary key value that is taken

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

INSERT INTO customers (id, email, name) VALUES (1, 'sara@example.com', 'Sara');
INSERT INTO customers (id, email, name) VALUES (1, 'ali@example.com', 'Ali');  -- id 1 already exists

UNIQUE constraint failed: customers.id

A duplicate in a multi-column key

CREATE TABLE order_lines (
    order_id INTEGER,
    line_no  INTEGER,
    PRIMARY KEY (order_id, line_no)
);

INSERT INTO order_lines (order_id, line_no) VALUES (10, 1);
INSERT INTO order_lines (order_id, line_no) VALUES (10, 1);

UNIQUE constraint failed: order_lines.order_id, order_lines.line_no

Adding a unique index over existing duplicates

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (name) VALUES ('Sara'), ('Sara');

CREATE UNIQUE INDEX ux_customers_name ON customers (name);

UNIQUE constraint failed: customers.name

How to prevent it

Let SQLite assign surrogate keys with INTEGER PRIMARY KEY, and make writes that may run twice idempotent: an upsert for "create or update" actions, ON CONFLICT DO NOTHING for imports. When data comes from several devices or servers, use a natural unique key (such as an email address or a UUID) rather than copying numeric ids between databases.

Keep the unique constraint even when the application checks first, and handle the constraint error in your code (in Python, catch sqlite3.IntegrityError and check the message) so a duplicate becomes a clear message such as "this email is already registered".

Common questions

Learn more on CodeWithSQL

• SQL constraints explained
• Primary key vs foreign key

Official documentation

• SQLite result and error codes
• UPSERT (INSERT ... ON CONFLICT)
• ON CONFLICT clause (OR REPLACE, OR IGNORE)
• ROWIDs and the INTEGER PRIMARY KEY

Last technically reviewed 24 October 2026; examples and diagnostics run on SQLite 3.39.2 (PHP PDO_SQLITE) and the sqlite3 shell 3.50.6. Messages are the text the SQLite library itself returns (sqlite3_errmsg) for the versions shown.