PRIMARY KEY must be unique

This error occurs when you try to insert a duplicate primary key value. Learn about auto-increment, composite keys, and handling duplicates.

The PRIMARY KEY must be unique error occurs when inserting a row with a primary key that already exists.

Understanding the Error

SQLITE_CONSTRAINT: PRIMARY KEY must be unique

Every row must have a unique primary key value.

Common Causes

1. Manual ID Already Exists

SQL

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');  -- Error: ID 1 exists

2. Reusing Deleted IDs

SQL

INSERT INTO users (id, name) VALUES (1, 'Alice');
DELETE FROM users WHERE id = 1;
-- Later, if another row got id = 1...
INSERT INTO users (id, name) VALUES (1, 'Carol');  -- Might conflict

3. Import/Sync Conflicts

Importing data that overlaps with existing records.

4. Composite Key Violation

SQL

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items VALUES (1, 100, 2);
INSERT INTO order_items VALUES (1, 100, 3);  -- Error: same order+product

How to Fix It

Solution 1: Use AUTOINCREMENT

Let SQLite handle ID generation:

SQL

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

-- Don't specify id
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');
-- IDs automatically assigned: 1, 2

Solution 2: Omit ID for Auto-Assignment

SQL

CREATE TABLE users (
  id INTEGER PRIMARY KEY,  -- Without AUTOINCREMENT
  name TEXT
);

INSERT INTO users (name) VALUES ('Alice');  -- Gets id = 1
INSERT INTO users (name) VALUES ('Bob');    -- Gets id = 2

Note: Without AUTOINCREMENT, deleted IDs may be reused.

Solution 3: Use INSERT OR REPLACE

SQL

-- Replace if exists
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Alice Updated');

Solution 4: Use INSERT OR IGNORE

SQL

-- Skip if duplicate
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Alice');

Solution 5: Use ON CONFLICT

SQL

INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

Solution 6: Check Before Insert

JAVASCRIPT

const exists = db.prepare(
  'SELECT 1 FROM users WHERE id = ?'
).get(id);

if (!exists) {
  db.run('INSERT INTO users (id, name) VALUES (?, ?)', [id, name]);
} else {
  // Handle duplicate
}

Solution 7: Use UUIDs

JAVASCRIPT

const { v4: uuid } = require('uuid');

// Virtually no chance of collision
const id = uuid();
db.run('INSERT INTO users (id, name) VALUES (?, ?)', [id, name]);

AUTOINCREMENT vs INTEGER PRIMARY KEY

SQL

-- INTEGER PRIMARY KEY: May reuse deleted IDs
CREATE TABLE t1 (id INTEGER PRIMARY KEY);

-- INTEGER PRIMARY KEY AUTOINCREMENT: Never reuses IDs
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT);

Best Practices

Use AUTOINCREMENT for most cases
Let database assign IDs when possible
Use UUIDs for distributed systems
Handle conflicts with ON CONFLICT
Validate data before bulk imports