How to fix SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed in SQLite

SQLiteINTERMEDIATEMEDIUMUpdated Sep 25, 2026· 443 views

This error occurs when you attempt to insert or update a row with a value that already exists in a column marked as UNIQUE. SQLite enforces uniqueness constraints and rejects duplicate values, preventing data inconsistency in your database.

Quick fix

Fixes SQLITE_CONSTRAINT_UNIQUE

-- Check if a record with this email already exists
SELECT COUNT(*) FROM users WHERE email = 'john@example.com';

-- If count is 0, safe to insert
IF count = 0 THEN
  INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');
END IF;
  1. Check if the record already exists before inserting
  2. Use INSERT OR REPLACE to overwrite existing records
  3. Use INSERT OR IGNORE to skip duplicates
  4. Use AUTOINCREMENT for primary keys to avoid manual conflicts
sqlite — error
-- Check if a record with this email already exists
SELECT COUNT(*) FROM users WHERE email = 'john@example.com';
-- If count is 0, safe to insert
IF count = 0 THEN
  INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');
END IF;
SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed

What this error means

The SQLITE_CONSTRAINT_UNIQUE error indicates that your SQL query violates a UNIQUE constraint on one or more columns in your table. A UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct—no two rows can have the same value for that column. When you attempt to INSERT or UPDATE a record with a value that already exists in a uniquely-constrained column, SQLite aborts the operation and raises this constraint violation error. The operation fails completely, and no changes are committed to the database unless you explicitly use a conflict resolution clause.

Typical symptoms

  • INSERT or UPDATE queries fail with "UNIQUE constraint failed" message
  • Application crashes or throws an exception when trying to save duplicate data
  • Duplicate key appears to be allowed, but database rejects the insert silently
  • Data integrity issues when syncing or importing data from other sources
  • Multiple NULL values in a UNIQUE column cause confusion (SQLite treats all NULLs as distinct)

Common causes

  1. Attempting to insert a row with an id or unique field value that already exists in the table
  2. Forgetting to check if a record exists before inserting or updating
  3. Not using auto-increment (AUTOINCREMENT or GENERATED ALWAYS AS IDENTITY) for primary keys, leading to manual id conflicts
  4. Importing or syncing data without deduplicating or checking for existing records first
  5. Multiple business processes inserting the same unique value concurrently without proper locking or transaction isolation
  6. Column was defined with UNIQUE constraint but application code does not account for this restriction

How to fix "SQLITE_CONSTRAINT_UNIQUE: UNIQUE constraint failed"

1Check if the record already exists before inserting

Before performing an INSERT, query the table to verify that the unique value does not already exist:

sql
-- Check if a record with this email already exists
SELECT COUNT(*) FROM users WHERE email = 'john@example.com';

-- If count is 0, safe to insert
IF count = 0 THEN
  INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');
END IF;

In Python with sqlite3:

python
import sqlite3
connection = sqlite3.connect('database.db')
cursor = connection.cursor()

# Check if email exists
cursor.execute('SELECT COUNT(*) FROM users WHERE email = ?', ('john@example.com',))
if cursor.fetchone()[0] == 0:
    cursor.execute('INSERT INTO users (id, email, name) VALUES (?, ?, ?)',
                   (1, 'john@example.com', 'John'))
    connection.commit()
2Use INSERT OR REPLACE to overwrite existing records

If you want to update an existing record when a duplicate is encountered, use the REPLACE conflict resolution algorithm. This removes the old row and inserts the new one:

sql
-- Will replace any existing user with email 'john@example.com'
REPLACE INTO users (id, email, name) VALUES (1, 'john@example.com', 'John Smith');

Or use the ON CONFLICT clause (modern SQLite 3.24.0+):

sql
INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John')
ON CONFLICT(email) DO UPDATE SET name = 'John';

In Python:

python
cursor.execute('''
  INSERT INTO users (id, email, name) VALUES (?, ?, ?)
  ON CONFLICT(email) DO UPDATE SET name = 'John'
''', (1, 'john@example.com', 'John'))
connection.commit()
3Use INSERT OR IGNORE to skip duplicates

If you want to silently skip inserting a duplicate record (without raising an error), use the IGNORE conflict resolution algorithm:

sql
-- Will skip this insert if email already exists, no error raised
INSERT OR IGNORE INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');

This is useful when batch-inserting data and you want to skip duplicates without stopping the entire operation.

python
cursor.execute('''
  INSERT OR IGNORE INTO users (id, email, name) VALUES (?, ?, ?)
''', (1, 'john@example.com', 'John'))
connection.commit()
4Use AUTOINCREMENT for primary keys to avoid manual conflicts

Instead of manually assigning id values, let SQLite auto-generate unique primary keys using AUTOINCREMENT:

sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
);

-- Don't specify id, let SQLite generate it
INSERT INTO users (email, name) VALUES ('john@example.com', 'John');
INSERT INTO users (email, name) VALUES ('jane@example.com', 'Jane');

In Python:

python
cursor.execute('''
  INSERT INTO users (email, name) VALUES (?, ?)
''', ('john@example.com', 'John'))
connection.commit()

Each insert automatically gets a unique id, eliminating id conflicts.

5Handle the exception in your application code

Wrap your INSERT/UPDATE operations in a try-except block to gracefully handle duplicates:

python
import sqlite3

connection = sqlite3.connect('database.db')
cursor = connection.cursor()

try:
    cursor.execute('''
      INSERT INTO users (id, email, name) VALUES (?, ?, ?)
    ''', (1, 'john@example.com', 'John'))
    connection.commit()
    print('User inserted successfully')
except sqlite3.IntegrityError as e:
    if 'UNIQUE constraint failed' in str(e):
        print('This email already exists. Updating instead...')
        cursor.execute('''
          UPDATE users SET name = ? WHERE email = ?
        ''', ('John', 'john@example.com'))
        connection.commit()
    else:
        raise

In JavaScript (Node.js with better-sqlite3):

javascript
const Database = require('better-sqlite3');
const db = new Database('database.db');

try {
  const stmt = db.prepare('INSERT INTO users (id, email, name) VALUES (?, ?, ?)');
  stmt.run(1, 'john@example.com', 'John');
} catch (error) {
  if (error.message.includes('UNIQUE constraint failed')) {
    console.log('Email already exists. Updating instead...');
    const updateStmt = db.prepare('UPDATE users SET name = ? WHERE email = ?');
    updateStmt.run('John', 'john@example.com');
  } else {
    throw error;
  }
}

Advanced notes

NULL values are treated as distinct: SQLite has a special behavior where NULL values are considered different from each other. This means a UNIQUE column can contain multiple NULL values without violating the constraint. If this is not the desired behavior, add a NOT NULL constraint.

Compound unique constraints: You can define a UNIQUE constraint across multiple columns, ensuring that combinations of values are unique:

sql
CREATE TABLE user_profiles (
  user_id INTEGER,
  platform TEXT,
  username TEXT,
  UNIQUE(user_id, platform)
);

-- OK: Same user on different platforms
INSERT INTO user_profiles VALUES (1, 'twitter', '@john');
INSERT INTO user_profiles VALUES (1, 'github', 'john123');

-- UNIQUE constraint failed: Duplicate user_id + platform combo
INSERT INTO user_profiles VALUES (1, 'twitter', '@john_dev');

Conflict resolution algorithms: SQLite supports five conflict resolution modes: ROLLBACK (default), ABORT (default for INSERT/UPDATE), FAIL, IGNORE, and REPLACE. You can specify these at the table level when creating the table, or at the statement level when executing INSERT/UPDATE/DELETE operations.

UPSERT (INSERT ... ON CONFLICT): Modern SQLite (3.24.0+) supports UPSERT syntax for atomic insert-or-update operations:

sql
INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John')
ON CONFLICT(id) DO UPDATE SET name = 'John';

Frequently asked questions

What does "SQLITE_CONSTRAINT_UNIQUE" mean?
The SQLITE_CONSTRAINT_UNIQUE error indicates that your SQL query violates a UNIQUE constraint on one or more columns in your table. A UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct—no two rows can have the same value for that column. When you attempt to INSERT or UPDATE a record with a value that already exists in a uniquely-constrained column, SQLite aborts the operation and raises this constraint violation error.
How do I fix "SQLITE_CONSTRAINT_UNIQUE"?
This error occurs when you attempt to insert or update a row with a value that already exists in a column marked as UNIQUE. SQLite enforces uniqueness constraints and rejects duplicate values, preventing data inconsistency in your database.
What causes "SQLITE_CONSTRAINT_UNIQUE"?
Attempting to insert a row with an id or unique field value that already exists in the table Forgetting to check if a record exists before inserting or updating Not using auto-increment (AUTOINCREMENT or GENERATED ALWAYS AS IDENTITY) for primary keys, leading to manual id conflicts
Is the "SQLITE_CONSTRAINT_UNIQUE" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading