How to fix SQLITE_CONSTRAINT_PRIMARYKEY: PRIMARY KEY constraint failed in SQLite

SQLiteINTERMEDIATEHIGHUpdated Sep 5, 2026· 248 views

This error occurs when you attempt to insert or update a row with a primary key value that already exists in the table. SQLite enforces primary key uniqueness strictly, rejecting duplicate key values to maintain data integrity and ensure each row is uniquely identifiable.

Quick fix

Fixes SQLITE_CONSTRAINT_PRIMARYKEY

-- Check if a record with this primary key already exists
SELECT COUNT(*) FROM users WHERE id = 1;

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

What this error means

The SQLITE_CONSTRAINT_PRIMARYKEY error (error code 1555) indicates that your SQL query violates the PRIMARY KEY constraint on a table. A PRIMARY KEY constraint ensures that each row in a table has a unique, non-null identifier that distinguishes it from all other rows. When you attempt to INSERT or UPDATE a record with a primary key value that already exists in the table, SQLite aborts the operation and raises this constraint violation error. The operation fails completely, and no changes are committed to the database unless you use a conflict resolution clause like ON CONFLICT or INSERT OR REPLACE.

Typical symptoms

  • INSERT or UPDATE queries fail with "PRIMARY KEY constraint failed" message
  • Application crashes or throws an IntegrityError when trying to save data with a duplicate key
  • Duplicate primary key appears to exist in the database (e.g., two rows with id=1)
  • Batch insert operations fail silently when one row violates the primary key constraint
  • Data migration or import scripts fail when encountering duplicate primary key values
  • ORM operations (like Room in Android or SQLAlchemy) throw constraint violation exceptions

Common causes

  1. Attempting to insert a row with an id value that already exists in the table
  2. Not using INTEGER PRIMARY KEY AUTOINCREMENT, leading to manual id conflicts when reusing deleted rows
  3. Manual assignment of primary key values without checking for existing rows first
  4. Multiple INSERT statements executing concurrently without proper transaction isolation or locking
  5. Importing or syncing data from another table without deduplicating or checking for existing records
  6. ORM or application code setting primary key to 0 or a default value during insert instead of letting SQLite auto-generate
  7. Restoring data from a backup without truncating the target table first, resulting in duplicate keys
  8. Incorrectly migrating data between tables without ensuring unique primary key values

How to fix "SQLITE_CONSTRAINT_PRIMARYKEY: PRIMARY KEY constraint failed"

1Check if a record with the primary key already exists

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

sql
-- Check if a record with this primary key already exists
SELECT COUNT(*) FROM users WHERE id = 1;

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

In Python with sqlite3:

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

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

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

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

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

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

In Python:

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

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

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

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, name, email) VALUES (?, ?, ?)
''', (1, 'John', 'john@example.com'))
connection.commit()
4Use INTEGER PRIMARY KEY AUTOINCREMENT to avoid manual conflicts

Instead of manually assigning id values, let SQLite auto-generate unique primary keys using AUTOINCREMENT. This prevents primary key conflicts when inserting new rows:

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

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

Key points:
- Use exactly "INTEGER PRIMARY KEY AUTOINCREMENT" (not INT or BIGINT)
- Don't pass an id value in your INSERT statement
- Each insert automatically gets a unique, incrementing id

In Python:

python
cursor.execute('''
  INSERT INTO users (name, email) VALUES (?, ?)
''', ('John', 'john@example.com'))
connection.commit()
print(f'New user id: {cursor.lastrowid}')
5Truncate the table and reimport data to remove duplicates

If your table has duplicate primary keys due to a data import error, truncate the table and re-import the data with duplicate handling:

sql
-- Delete all rows from the table
DELETE FROM users;

-- Optionally reset the autoincrement counter
DELETE FROM sqlite_sequence WHERE name='users';

-- Now reimport data using INSERT OR IGNORE or INSERT OR REPLACE
INSERT OR IGNORE INTO users (id, name, email) 
SELECT id, name, email FROM users_temp;

In Python:

python
# Delete existing data
cursor.execute('DELETE FROM users')
cursor.execute("DELETE FROM sqlite_sequence WHERE name='users'")
connection.commit()

# Re-import with conflict handling
cursor.execute('''
  INSERT OR IGNORE INTO users (id, name, email)
  SELECT id, name, email FROM users_temp
''')
connection.commit()
6Handle the exception in your application code

Wrap your INSERT/UPDATE operations in a try-except block to gracefully handle primary key constraint violations:

python
import sqlite3

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

try:
    cursor.execute('''
      INSERT INTO users (id, name, email) VALUES (?, ?, ?)
    ''', (1, 'John', 'john@example.com'))
    connection.commit()
    print('User inserted successfully')
except sqlite3.IntegrityError as e:
    if 'PRIMARY KEY constraint failed' in str(e):
        print(f'User with id 1 already exists. Updating instead...')
        cursor.execute('''
          UPDATE users SET name = ?, email = ? WHERE id = ?
        ''', ('John', 'john@example.com', 1))
        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, name, email) VALUES (?, ?, ?)');
  stmt.run(1, 'John', 'john@example.com');
  console.log('User inserted successfully');
} catch (error) {
  if (error.message.includes('PRIMARY KEY constraint failed')) {
    console.log('User with id 1 already exists. Updating instead...');
    const updateStmt = db.prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
    updateStmt.run('John', 'john@example.com', 1);
  } else {
    throw error;
  }
}

Advanced notes

ROWID and INTEGER PRIMARY KEY: In SQLite, a column defined as INTEGER PRIMARY KEY is an alias for the internal ROWID. When you don't use AUTOINCREMENT, SQLite automatically reuses ROWIDs from deleted rows. This can cause primary key conflicts if you're not careful. AUTOINCREMENT prevents this reuse but comes with minor performance overhead.

sqlite_sequence table: When you use AUTOINCREMENT, SQLite maintains an internal table called sqlite_sequence that tracks the highest ROWID ever used. You can inspect or reset it:

sql
-- View the current autoincrement counter
SELECT * FROM sqlite_sequence WHERE name='users';

-- Reset the counter to 100
UPDATE sqlite_sequence SET seq = 100 WHERE name='users';

-- Delete to reset to 0
DELETE FROM sqlite_sequence WHERE name='users';

Composite PRIMARY KEYs: You can define a primary key across multiple columns to enforce uniqueness on combinations of values:

sql
CREATE TABLE user_profiles (
  user_id INTEGER,
  platform TEXT,
  username TEXT,
  PRIMARY KEY (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');

-- PRIMARY KEY constraint failed: Duplicate (user_id, platform) combination
INSERT INTO user_profiles VALUES (1, 'twitter', '@john_dev');

ON CONFLICT clause: Modern SQLite (3.24.0+) supports the ON CONFLICT clause for atomic insert-or-update operations:

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

-- Or do nothing if a conflict occurs
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com')
ON CONFLICT(id) DO NOTHING;

ORM-specific issues: Some ORMs (like Room in Android or certain versions of SQLAlchemy) may incorrectly set the primary key to 0 or a default value during insert instead of letting SQLite auto-generate it. This causes every inserted row to overwrite the first row. Check your ORM documentation for how to properly omit primary key values during insert.

Frequently asked questions

What does "SQLITE_CONSTRAINT_PRIMARYKEY" mean?
The SQLITE_CONSTRAINT_PRIMARYKEY error (error code 1555) indicates that your SQL query violates the PRIMARY KEY constraint on a table. A PRIMARY KEY constraint ensures that each row in a table has a unique, non-null identifier that distinguishes it from all other rows. When you attempt to INSERT or UPDATE a record with a primary key value that already exists in the table, SQLite aborts the operation and raises this constraint violation error.
How do I fix "SQLITE_CONSTRAINT_PRIMARYKEY"?
This error occurs when you attempt to insert or update a row with a primary key value that already exists in the table. SQLite enforces primary key uniqueness strictly, rejecting duplicate key values to maintain data integrity and ensure each row is uniquely identifiable.
What causes "SQLITE_CONSTRAINT_PRIMARYKEY"?
Attempting to insert a row with an id value that already exists in the table Not using INTEGER PRIMARY KEY AUTOINCREMENT, leading to manual id conflicts when reusing deleted rows Manual assignment of primary key values without checking for existing rows first
Is the "SQLITE_CONSTRAINT_PRIMARYKEY" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading