How to fix ERROR 1062: Duplicate entry for key in MySQL
Error 1062 occurs when you try to insert or update a record with a value that already exists in a column with a unique constraint (primary key or unique index). This can be fixed by checking for existing records, using INSERT...ON DUPLICATE KEY UPDATE, or properly managing your auto-increment values.
Quick fix
Fixes ERROR 1062: Duplicate entry for key
-- Example error output:
-- ERROR 1062 (23000): Duplicate entry "42" for key "users.id"- Identify which column is causing the duplicate key error
- Check if the duplicate value actually exists in the table
- Use INSERT...ON DUPLICATE KEY UPDATE for "upsert" behavior
- Or, use INSERT IGNORE to silently skip duplicates
-- Example error output:-- ERROR 1062 (23000): Duplicate entry "42" for key "users.id"ERROR 1062: Duplicate entry for key
What this error means
MySQL Error 1062 (SQLSTATE 23000) is an integrity constraint violation that occurs when you attempt to insert or update data with a value that already exists in a field protected by a unique constraint. This constraint can be a PRIMARY KEY, a UNIQUE INDEX, or a UNIQUE KEY. MySQL is designed to enforce data uniqueness for these fields, so any attempt to introduce duplicate values is immediately rejected. The error message includes the duplicate value and the key name, helping you identify which field caused the violation.
Typical symptoms
- INSERT or UPDATE queries fail with "ERROR 1062 (23000): Duplicate entry"
- Database operations stop unexpectedly during bulk imports or migrations
- Web applications crash or hang when saving user data (login, registration)
- Replication fails between MySQL master and slave servers
- Data migrations from staging to production abort mid-process
Common causes
- Attempting to insert a record with a PRIMARY KEY that already exists in the table
- A UNIQUE INDEX or UNIQUE KEY constraint is violated by new data matching existing values
- Auto-increment primary key has been manually set to a value that already exists
- Application code generates or uses duplicate identifiers without checking first
- Data type for auto-increment column (e.g., TINYINT) reached its maximum value and wrapped around to existing IDs
- Case-insensitive collation treating different-case values as duplicates ("John" vs "john")
- Replication issues causing the same row to be inserted multiple times from different sources
- Corrupted or incomplete database dump files containing duplicate rows
How to fix "ERROR 1062: Duplicate entry for key"
The error message shows you the key name: ERROR 1062 (...) for key 'column_name'. Note this value. If it says "PRIMARY", the duplicate is in your primary key column. If it shows a custom index name, that's your unique constraint.
-- Example error output:
-- ERROR 1062 (23000): Duplicate entry "42" for key "users.id"Query the table to see if the value you're trying to insert already exists:
-- For a primary key violation:
SELECT * FROM users WHERE id = 42;
-- For a unique index violation:
SELECT * FROM users WHERE email = 'user@example.com';If the query returns a row, the duplicate exists and must be handled before proceeding.
Instead of failing on a duplicate, use the ON DUPLICATE KEY UPDATE clause to update the existing row:
-- Insert new user or update if email already exists:
INSERT INTO users (id, name, email, last_login)
VALUES (42, "John Doe", "john@example.com", NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
last_login = VALUES(last_login);This works for PRIMARY KEY and UNIQUE INDEX violations. The old row is updated instead of rejecting the insert.
If you want to ignore duplicates rather than update them:
-- Silently skip if duplicate:
INSERT IGNORE INTO users (id, name, email)
VALUES (42, "John Doe", "john@example.com");This demotes the error to a warning and doesn't insert or update the row. Useful for bulk imports where some records may already exist.
Never manually set an auto-increment primary key to a specific value unless you know it's unique. Instead, let MySQL generate the value:
-- Bad (may cause duplicate if 42 exists):
INSERT INTO users (id, name) VALUES (42, "John Doe");
-- Good (MySQL auto-generates the ID):
INSERT INTO users (name) VALUES ("John Doe");
-- Or explicitly use NULL:
INSERT INTO users (id, name) VALUES (NULL, "John Doe");If you manually set IDs, ensure you know the next safe value. Use SELECT MAX(id) FROM users; to find the highest ID in use.
If you're using TINYINT for your primary key, it maxes out at 127. When a new auto-increment tries to use 128, MySQL wraps it back to 127, causing a duplicate:
-- Check current data type:
DESC users;
-- Upgrade to INT (safe for ~2 billion records):
ALTER TABLE users MODIFY id INT AUTO_INCREMENT;
-- Or use BIGINT for massive datasets:
ALTER TABLE users MODIFY id BIGINT AUTO_INCREMENT;MySQL's default collation is case-insensitive. This means "John" and "john" are treated as the same value for unique constraints:
-- Check current collation:
SHOW CREATE TABLE users;
-- If case matters, use a binary collation:
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin UNIQUE;Before inserting into production, load data into a temporary staging table and de-duplicate:
-- Create staging table with same structure:
CREATE TEMPORARY TABLE users_staging LIKE users;
-- Load data from file or source:
LOAD DATA INFILE '/data/users.csv' INTO TABLE users_staging ...
-- Remove duplicates within staging table:
DELETE FROM users_staging
WHERE id NOT IN (
SELECT MIN(id) FROM users_staging GROUP BY email
);
-- Now insert safely:
INSERT IGNORE INTO users SELECT * FROM users_staging;Advanced notes
For MySQL replication environments, error 1062 can indicate that the same row is being inserted from multiple master servers. This happens when using multi-source replication without proper server IDs or row-level binlogging. Use SHOW SLAVE STATUS\G to check replication lag and SET sql_slave_skip_counter = 1; to skip a single errant statement, then verify replication continues. If dealing with very large tables (billions of rows), consider using PARTITION BY to isolate data and use range-based unique keys instead of pure auto-increment. For application-level handling in Python, catch mysql.connector.errors.IntegrityError exceptions; in Node.js/JavaScript, check for error code 1062 in the error object. Some ORMs (like Sequelize, TypeORM, Prisma) provide built-in support for handling duplicates gracefully with upsert operations.
Frequently asked questions
- What does "ERROR 1062: Duplicate entry for key" mean?
- MySQL Error 1062 (SQLSTATE 23000) is an integrity constraint violation that occurs when you attempt to insert or update data with a value that already exists in a field protected by a unique constraint. This constraint can be a PRIMARY KEY, a UNIQUE INDEX, or a UNIQUE KEY. MySQL is designed to enforce data uniqueness for these fields, so any attempt to introduce duplicate values is immediately rejected.
- How do I fix "ERROR 1062: Duplicate entry for key"?
- Error 1062 occurs when you try to insert or update a record with a value that already exists in a column with a unique constraint (primary key or unique index). This can be fixed by checking for existing records, using INSERT...ON DUPLICATE KEY UPDATE, or properly managing your auto-increment values.
- What causes "ERROR 1062: Duplicate entry for key"?
- Attempting to insert a record with a PRIMARY KEY that already exists in the table A UNIQUE INDEX or UNIQUE KEY constraint is violated by new data matching existing values Auto-increment primary key has been manually set to a value that already exists
- Is the "ERROR 1062: Duplicate entry for key" error serious?
- It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.
Official resources & further reading
- 📚MySQL Official Documentation
- 📖MySQL 8.0 Error Reference(Official Docs)
- 📖MySQL INSERT...ON DUPLICATE KEY UPDATE Statement(Official Docs)