UNIQUE constraint failed

What it means

A UNIQUE constraint, a PRIMARY KEY or a unique index says no two rows may share a value, and your INSERT or UPDATE would have created a second one. SQLite stops that statement, undoes what the statement changed, and leaves earlier statements in your transaction as they were.

The message tells you where to look:

• One column: UNIQUE constraint failed: users.email.
• A constraint over several columns lists them all: users.team, users.handle.
• A unique index on an expression names the index: index 'tags_name_lower'.

The primary result code is SQLITE_CONSTRAINT (19). The extended code is SQLITE_CONSTRAINT_UNIQUE (2067), or SQLITE_CONSTRAINT_PRIMARYKEY (1555) when the duplicate is the primary key; the message reads the same for both.

Common causes

1. The row already exists. An import or seed script ran twice, a retry repeated an insert that had worked, or a form was submitted twice.
2. An explicit id that’s taken. Copying rows between databases, or fixtures with hard-coded ids.
3. An UPDATE sets a value another row already has, such as changing a user’s email to one in use.
4. Adding a unique index to a table that has duplicates. CREATE UNIQUE INDEX fails with the same message.
5. Values that differ but compare equal in the index, such as a unique index on lower(name) or a column with COLLATE NOCASE.

How to fix it

Find the existing row

Use the table and column from the message:

SELECT * FROM users WHERE email = 'ada@example.com';

If the row is the one you meant to write, the insert is redundant: update it instead, or skip it.

Insert or update in one statement

An upsert (SQLite 3.24.0 and later) handles both cases. excluded is the row you tried to insert:

INSERT INTO users (email, team) VALUES ('ada@example.com', 'data')
ON CONFLICT (email) DO UPDATE SET team = excluded.team;

To keep the existing row and ignore the new one, use ON CONFLICT (email) DO NOTHING. INSERT OR IGNORE does the same, but it also silently skips rows that break NOT NULL or CHECK constraints, which can hide bad data.

Be careful with INSERT OR REPLACE

REPLACE deletes the existing row and inserts a new one. Columns you didn’t supply aren’t kept, and the row can get a new rowid. In the test below, replacing Ada’s row by email moved it from id 1 to id 4 and emptied its team and handle. Prefer ON CONFLICT … DO UPDATE unless that’s what you want.

Let SQLite choose the id

For an INTEGER PRIMARY KEY, leave the column out of the INSERT (or pass NULL) and SQLite picks the next free value.

Remove duplicates before adding a unique index

Find them first:

SELECT email, count(*) FROM people GROUP BY email HAVING count(*) > 1;

Decide which copy to keep. To keep the oldest of each, then add the index:

DELETE FROM people WHERE rowid NOT IN (SELECT min(rowid) FROM people GROUP BY email);
CREATE UNIQUE INDEX people_email ON people (email);

Decide whether case matters

By default SQLite compares text exactly, so ADA@example.com and ada@example.com are both allowed. To treat them as the same value, declare the column UNIQUE COLLATE NOCASE (or create the index with COLLATE NOCASE), and expect this error for values that differ only in case.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  team TEXT, handle TEXT,
  UNIQUE (team, handle)
);
INSERT INTO users (email, team, handle) VALUES ('ada@example.com', 'core', 'ada');
INSERT INTO users (email) VALUES ('ada@example.com');

Error: stepping, UNIQUE constraint failed: users.email (19)

The shell adds “Error: stepping,” and the result code; SQLite’s message is UNIQUE constraint failed: users.email. The other forms:

Error: stepping, UNIQUE constraint failed: users.team, users.handle (19)
Error: stepping, UNIQUE constraint failed: users.id (19)
Error: stepping, UNIQUE constraint failed: index 'tags_name_lower' (19)

Creating a unique index over a column that already had x@example.com twice:

Error: stepping, UNIQUE constraint failed: people.email (19)

After the DELETE … NOT IN (SELECT min(rowid) …) above, the same CREATE UNIQUE INDEX succeeded. Through Python’s sqlite3 module (SQLite 3.53.4), the extended codes were SQLITE_CONSTRAINT_UNIQUE for the email and SQLITE_CONSTRAINT_PRIMARYKEY for the duplicate id.

In Inlet

Grid edits are staged until you commit (⌘S), and Review shows the exact SQL first, so you can see which INSERT or UPDATE carries the repeated value. When SQLite refuses it, Inlet shows SQLite’s message with the table and column.