Menu

MySQL Error 1061: Duplicate Key Name (42000)

Fix MySQL Error 1061 by inspecting existing indexes and migrations before adding or renaming an index. Learn how it differs from duplicate rows.

Posted on By
On this page

MySQL Error 1061 (42000, ER_DUP_KEYNAME) means a statement tried to create an index with a name already used by an index on the target table. The message commonly looks like this:

ERROR 1061 (42000): Duplicate key name 'idx_orders_email'

This is about the index name, not duplicate data in indexed columns. The MySQL 8.4 error reference lists Error 1061 and its SQLSTATE.

Inspect the indexes on the exact table

Check the target table, including its database, before repeating the statement:

SHOW INDEX FROM `app_db`.`orders`;
SHOW CREATE TABLE `app_db`.`orders`;

In the output, Key_name is the index name. Compare its columns, column order, and whether it is unique with the index your migration or CREATE INDEX statement intends to add. The SHOW INDEX documentation describes these fields.

Index names must be unique within a table. PRIMARY is reserved for the table’s primary key. The same secondary index name can be used on a different table.

Choose the fix that matches your intent

The intended index already exists

If the existing index has the definition the application needs, remove the duplicate CREATE INDEX or ALTER TABLE ... ADD INDEX operation from the migration or setup script. Check migration history so a successful change is not applied twice.

The name exists, but the index definition is different

Do not assume that an index is correct just because its name matches. Compare its columns and uniqueness. If a schema change is intended, plan the drop and replacement as one deliberate migration; dropping an index can affect query performance and may be restricted when the index supports a constraint.

If the old index should remain and the new index serves a different purpose, choose a distinct name. If you are renaming an index, use ALTER TABLE ... RENAME INDEX with a name not already in use; see the MySQL ALTER TABLE guide.

The statement is creating a table

If a CREATE TABLE definition names the same index more than once, keep one definition or assign distinct names to the intended indexes. A primary key is always named PRIMARY, so it cannot also be used as the name of a secondary index.

For index creation syntax, see the MySQL CREATE INDEX tutorial. Browse more fixes in MySQL error troubleshooting.