How to fix SQLITE_CONSTRAINT_PRIMARYKEY: PRIMARY KEY constraint failed in SQLite

-- 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', '[email protected]');
END IF;

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', '[email protected]'))
    connection.commit()
else:
    print('Record with id 1 already exists')

-- Will replace any existing user with id 1
REPLACE INTO users (id, name, email) VALUES (1, 'John Smith', '[email protected]');

INSERT INTO users (id, name, email) VALUES (1, 'John Smith', '[email protected]')
ON CONFLICT(id) DO UPDATE SET name = 'John Smith', email = '[email protected]';

cursor.execute('''
  INSERT INTO users (id, name, email) VALUES (?, ?, ?)
  ON CONFLICT(id) DO UPDATE SET name = ?, email = ?
''', (1, 'John Smith', '[email protected]', 'John Smith', '[email protected]'))
connection.commit()

-- Will skip this insert if id 1 already exists, no error raised
INSERT OR IGNORE INTO users (id, name, email) VALUES (1, 'John', '[email protected]');

cursor.execute('''
  INSERT OR IGNORE INTO users (id, name, email) VALUES (?, ?, ?)
''', (1, 'John', '[email protected]'))
connection.commit()

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', '[email protected]');
INSERT INTO users (name, email) VALUES ('Jane', '[email protected]');
-- id values automatically become 1, 2, 3, etc.

cursor.execute('''
  INSERT INTO users (name, email) VALUES (?, ?)
''', ('John', '[email protected]'))
connection.commit()
print(f'New user id: {cursor.lastrowid}')

-- 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;

# 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()

import sqlite3

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

try:
    cursor.execute('''
      INSERT INTO users (id, name, email) VALUES (?, ?, ?)
    ''', (1, 'John', '[email protected]'))
    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', '[email protected]', 1))
        connection.commit()
    else:
        raise

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', '[email protected]');
  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', '[email protected]', 1);
  } else {
    throw error;
  }
}