MySQL Error 1062 - Duplicate Entry

What It Means

MySQL error 1062 (ER_DUP_ENTRY) means “Duplicate entry ‘value’ for key ‘key_name’.” An INSERT or UPDATE statement tried to store a value that already exists in a column (or combination of columns) with a UNIQUE or PRIMARY KEY constraint.

Common Causes

• Inserting a row with a primary key that already exists
• Inserting duplicate values in a column with a UNIQUE index
• Auto-increment counter collision after data import
• Application retrying a failed insert that actually succeeded
• Missing duplicate handling in bulk inserts
• Composite unique key violation

How to Fix

Use INSERT … ON DUPLICATE KEY UPDATE

-- Update the existing row if a duplicate is found
INSERT INTO users (email, name, updated_at)
VALUES ('john@example.com', 'John Doe', NOW())
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  updated_at = VALUES(updated_at);

Use INSERT IGNORE

-- Silently skip duplicates (use with caution)
INSERT IGNORE INTO users (email, name)
VALUES ('john@example.com', 'John Doe');
-- No error, but the row is NOT inserted if email already exists

Use REPLACE INTO

-- Delete existing row and insert new one (use with caution)
REPLACE INTO users (email, name)
VALUES ('john@example.com', 'John Updated');
-- Warning: this deletes and re-inserts, changing the auto-increment ID

Check for existing records first

// Node.js with mysql2
async function createUser(email, name) {
  try {
    await db.query(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [email, name]
    );
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      throw new Error(`User with email ${email} already exists`);
    }
    throw error;
  }
}

# Python with mysql-connector
import mysql.connector

try:
    cursor.execute(
        "INSERT INTO users (email, name) VALUES (%s, %s)",
        (email, name)
    )
    conn.commit()
except mysql.connector.IntegrityError as e:
    if e.errno == 1062:
        print(f"Duplicate entry: {e}")
    else:
        raise

Fix auto-increment issues

-- Reset auto-increment to the next available value
ALTER TABLE users AUTO_INCREMENT = 1;

-- Or set it to a specific value after data import
SELECT MAX(id) FROM users;  -- Find the current max
ALTER TABLE users AUTO_INCREMENT = 1001;  -- Set higher than max

Find and resolve duplicates

-- Find duplicate values
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING count > 1;

-- Remove duplicates keeping the first occurrence
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

Related Errors

• MySQL 1045 - Access denied: Authentication issues unrelated to data constraints.
• MySQL 1146 - Table doesn’t exist: The target table is missing.
• MySQL 1451 - Foreign key constraint: Another type of constraint violation.