How to Fix MySQL Error 1061: Duplicate Key Name

ERROR 1061 (42000): Duplicate key name '<index_name>' is raised when you attempt to create an index whose name is already used by an existing index on the same table. The error symbol is ER_DUP_KEYNAME.

Impact

The ALTER TABLE or CREATE TABLE statement fails immediately and no index is created. If the command was part of a multi-step migration, the migration will halt at that point and subsequent steps will not execute. The table's existing schema and data are left untouched, so there is no data loss, but the intended index will be absent.

Developers most commonly encounter this error during schema migrations — either running a migration that was already applied to a database, or running two separate migration scripts that both try to add an index with the same name on the same table. ORMs such as Hibernate and frameworks like Rails or Laravel surface it as a migration failure with the underlying MySQL error message included.

Common Causes

Running a migration twice. A migration that adds an index was executed successfully once, and then executed again (for example, due to a failed migration tracking record). The index already exists, so the second run fails.
Index name collision across independent migrations. Two separate migration files — often written by different developers — both add an index to the same table and happen to choose the same index name (e.g., both name it idx_user_id).
Naming an index explicitly with a name already used by a PRIMARY KEY or UNIQUE constraint. MySQL creates a named index behind every UNIQUE constraint and every PRIMARY KEY. Trying to add a regular index with one of those names will trigger this error.
Restoring a dump onto a database that already contains the table. Importing a mysqldump into a schema that already has the table with its indexes causes the CREATE TABLE (or the ALTER TABLE statements inside the dump) to fail if the target table was not dropped first.
Migrating from a different storage engine or schema version. Engine-level or version-level differences in how indexes were created can leave behind index names that look new but are already present in INFORMATION_SCHEMA.

Troubleshooting and Resolution Steps

List all existing indexes on the table to see which names are already in use:

SHOW INDEX FROM your_table_name;

Or query INFORMATION_SCHEMA for a cleaner view:

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE, SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'your_table_name'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

If the index already exists and covers the right columns, the migration is a no-op — skip it or mark it as already applied:
```
-- Verify the existing index covers the columns you intended
SHOW INDEX FROM your_table_name WHERE Key_name = 'idx_the_duplicate_name';
```
If it matches your intent, no action is needed. Mark the migration as done in your migration tool.

If the existing index is different and you need both, rename the new index in your migration:

-- Use a more specific name to avoid future collisions
ALTER TABLE your_table_name ADD INDEX idx_your_table_column_v2 (column_name);

If the existing index is a leftover from a previous failed run, drop it and re-add it with the correct definition:

ALTER TABLE your_table_name DROP INDEX idx_the_duplicate_name;
ALTER TABLE your_table_name ADD INDEX idx_the_duplicate_name (column_name);

Use IF NOT EXISTS (MySQL 8.0.29+) to make the migration idempotent:
```
ALTER TABLE your_table_name ADD INDEX IF NOT EXISTS idx_column_name (column_name);
```
On older MySQL versions, guard with a conditional in a stored procedure or handle it in application-level migration code by checking INFORMATION_SCHEMA first.

Check for PRIMARY KEY and UNIQUE constraint names. The primary key index is always named PRIMARY. Unique constraints use the constraint name as their index name. Avoid those names for regular indexes:

-- This fails if a UNIQUE KEY named 'email' already exists on the table
ALTER TABLE users ADD INDEX email (email);
-- Rename to avoid the collision
ALTER TABLE users ADD INDEX idx_users_email (email);

Additional Information

SQLSTATE 42000 indicates a syntax or access violation class of error; ER_DUP_KEYNAME is specifically about object-naming conflicts, not data duplication.
This error is unrelated to ERROR 1062 (23000): Duplicate entry (ER_DUP_ENTRY), which is raised when inserting or updating a row that violates a unique index. 1061 is a DDL-time error; 1062 is a DML-time error.
In MariaDB the same error code and symbol are used with identical semantics.
Migration frameworks (Flyway, Liquibase, Django, Rails) all expose the raw MySQL error message. Check the migration log for the exact index name in the error text to quickly locate the conflicting migration file.
When using CREATE TABLE ... LIKE or CREATE TABLE ... SELECT, indexes are copied along with the table; adding indexes with the same names afterward will produce this error.

Frequently Asked Questions

What is the difference between MySQL error 1061 and error 1062? Error 1061 (ER_DUP_KEYNAME) is a schema-level error that occurs when two index definitions share the same name. Error 1062 (ER_DUP_ENTRY) is a data-level error that occurs when an INSERT or UPDATE would create a duplicate value in a unique index. They happen at different stages: 1061 during DDL, 1062 during DML.

Can I rename an existing index instead of dropping and re-creating it? Yes, in MySQL 5.7+ you can use ALTER TABLE ... RENAME INDEX:

ALTER TABLE your_table_name RENAME INDEX old_index_name TO new_index_name;

This is a metadata-only change and does not rebuild the index, so it is very fast.

Why does this error appear even though I never explicitly created an index with that name? MySQL automatically creates a named index whenever you define a UNIQUE constraint or a PRIMARY KEY. If you declared UNIQUE KEY email (email), MySQL created an index internally named email. Attempting to add another index named email later triggers error 1061.

How do I make a migration safe to re-run (idempotent) on MySQL versions older than 8.0.29? Check INFORMATION_SCHEMA.STATISTICS before adding the index:

SET @index_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'your_table_name'
    AND INDEX_NAME = 'idx_column_name'
);
-- Run conditionally in application migration code or a stored procedure

Most migration frameworks allow you to write a code-based migration (rather than a plain SQL file) where you can query for the index before adding it.