<p>The error appears when you create or modify a UNIQUE or PRIMARY KEY and MySQL detects duplicate values in the target columns.</p>
<p>MySQL Error 1859 ER_DUP_UNKNOWN_IN_INDEX occurs when a UNIQUE or PRIMARY KEY you are adding contains duplicate values. Deduplicate the offending rows, then rerun the ALTER or CREATE statement to eliminate the error.</p>
Duplicate entry for key '%s'
The database returns error code 1859 with message 'Duplicate entry for key' when you attempt to create or change a UNIQUE index or PRIMARY KEY that would contain duplicate values. MySQL aborts the statement to protect data integrity.
The error was introduced in MySQL 5.7.1 and inherits SQLSTATE 23000 which denotes constraint violations. Fixing it is critical because the failing DDL leaves your schema in its previous state and may block deployments.
The most common trigger is an ALTER TABLE that adds a unique constraint to a column set that already has duplicate rows. MySQL scans existing data during the DDL operation and raises the error as soon as the first duplicate appears.
The same check runs when you insert or update rows into a table that has a UNIQUE index in place but where earlier bad data slipped in through disabled checks or bulk imports.
First locate the duplicate values with GROUP BY or window functions. Next, remove or update the offending rows so each key combination is unique. Finally, rerun the ALTER TABLE or INSERT that previously failed.
For OLTP systems with minimal downtime, move duplicates to a staging table inside a transaction, commit the clean set, then recreate the unique index. Galaxy's AI copilot can generate the deduplication query and share it with teammates for review.
Schema migrations managed by tools like Liquibase often fail in CI when historical test data contains duplicates. Add a pre-migration cleanup script that deletes or merges duplicates before the DDL step.
Bulk CSV imports using LOAD DATA can bypass foreign key checks, leaving duplicates that surface later. Always import into a temporary table, run DISTINCT inserts into the target, and validate with NOT EXISTS checks.
Design tables with the correct UNIQUE constraints from day one so duplicates never accumulate. Use INSERT ... ON DUPLICATE KEY UPDATE for idempotent upserts instead of blind INSERTs.
Monitor information_schema.statistics and run periodic COUNT DISTINCT audits. Galaxy Collections let teams publish canonical queries that flag duplicate keys and schedule them as recurring checks.
Error 1062 Duplicate entry for key occurs when the index name is known at runtime, while 1859 indicates MySQL could not identify which pending index caused the clash during DDL. The remediation steps are the same: locate and remove duplicates, then reapply the constraint.
Columns targeted by a new UNIQUE index already contain duplicate values.
The chosen column combination is not truly unique across historical data.
Data loaded with disabled unique checks allowed duplicates to slip in.
Race conditions inserted identical keys before the index was added.
Occurs during INSERT or UPDATE when a value violates an existing UNIQUE index.
Seen in MySQL when handling foreign data wrapper replication duplicates.
Older alias for duplicate key errors during INSERT SELECT operations.
No. It can also appear during CREATE TABLE ... SELECT or online ADD INDEX statements when duplicates exist.
Use GROUP BY with HAVING COUNT(*) > 1 or temporary UNIQUE index with IGNORE to let MySQL report duplicates.
No. The setting relates to date validation, not uniqueness checks.
Using ALTER TABLE IGNORE created in old versions is deprecated and removed in MySQL 8.0. Clean data instead.
.avif)
.avif){kind=logo}
