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.
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.
Before performing an INSERT, query the table to verify that the unique value does not already exist:
-- Check if a record with this email already exists
SELECT COUNT(*) FROM users WHERE email = '[email protected]';
-- If count is 0, safe to insert
IF count = 0 THEN
INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'John');
END IF;In Python with sqlite3:
import sqlite3
connection = sqlite3.connect('database.db')
cursor = connection.cursor()
# Check if email exists
cursor.execute('SELECT COUNT(*) FROM users WHERE email = ?', ('[email protected]',))
if cursor.fetchone()[0] == 0:
cursor.execute('INSERT INTO users (id, email, name) VALUES (?, ?, ?)',
(1, '[email protected]', 'John'))
connection.commit()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:
-- Will replace any existing user with email '[email protected]'
REPLACE INTO users (id, email, name) VALUES (1, '[email protected]', 'John Smith');Or use the ON CONFLICT clause (modern SQLite 3.24.0+):
INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'John')
ON CONFLICT(email) DO UPDATE SET name = 'John';In Python:
cursor.execute('''
INSERT INTO users (id, email, name) VALUES (?, ?, ?)
ON CONFLICT(email) DO UPDATE SET name = 'John'
''', (1, '[email protected]', 'John'))
connection.commit()If you want to silently skip inserting a duplicate record (without raising an error), use the IGNORE conflict resolution algorithm:
-- Will skip this insert if email already exists, no error raised
INSERT OR IGNORE INTO users (id, email, name) VALUES (1, '[email protected]', 'John');This is useful when batch-inserting data and you want to skip duplicates without stopping the entire operation.
cursor.execute('''
INSERT OR IGNORE INTO users (id, email, name) VALUES (?, ?, ?)
''', (1, '[email protected]', 'John'))
connection.commit()Instead of manually assigning id values, let SQLite auto-generate unique primary keys using AUTOINCREMENT:
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 ('[email protected]', 'John');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Jane');In Python:
cursor.execute('''
INSERT INTO users (email, name) VALUES (?, ?)
''', ('[email protected]', 'John'))
connection.commit()Each insert automatically gets a unique id, eliminating id conflicts.
Wrap your INSERT/UPDATE operations in a try-except block to gracefully handle duplicates:
import sqlite3
connection = sqlite3.connect('database.db')
cursor = connection.cursor()
try:
cursor.execute('''
INSERT INTO users (id, email, name) VALUES (?, ?, ?)
''', (1, '[email protected]', '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', '[email protected]'))
connection.commit()
else:
raiseIn JavaScript (Node.js with better-sqlite3):
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, '[email protected]', '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', '[email protected]');
} else {
throw error;
}
}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:
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:
INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'John')
ON CONFLICT(id) DO UPDATE SET name = 'John';SQLITE_BUSY: The database file is locked
How to fix 'SQLITE_BUSY: The database file is locked' in SQLite
better-sqlite3: This statement has already been finalized
How to fix "better-sqlite3: This statement has already been finalized" in SQLite
SQLITE_AUTH: Authorization denied
SQLITE_AUTH: Authorization denied
SQLITE_CONSTRAINT_CHECK: CHECK constraint failed
CHECK constraint failed in SQLite
SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified
How to fix "SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified" in SQLite