EC
1061
MySQLERRORCommonDDLHIGH confidence

Duplicate key name in CREATE/ALTER TABLE

Production Risk

Low — DDL fails; existing indexes are unchanged.

What this means

ER_DUP_KEYNAME (1061, SQLSTATE 42000) is raised when CREATE TABLE or ALTER TABLE attempts to create two indexes with the same name on the same table.

Why it happens
  1. 1Migration script adds an index without checking if it already exists
  2. 2Two ALTER TABLE statements both create an index with the same name
  3. 3ORM generates duplicate index names on schema sync
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, INDEX idx_id (id), INDEX idx_id (id));

expected output

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

Fix

Check for existing index before adding

WHEN In migration scripts that may run multiple times.

Check for existing index before adding
-- Check first:
SHOW INDEX FROM t WHERE Key_name = 'idx_id';

-- In MySQL 8.0 you can use IF NOT EXISTS with ALTER TABLE:
ALTER TABLE t ADD INDEX IF NOT EXISTS idx_id (id);

Why this works

IF NOT EXISTS on ADD INDEX (MySQL 8.0+) makes the operation idempotent.

What not to do

✕ Drop all indexes and recreate them to avoid 1061

Dropping and recreating indexes on large tables is expensive and causes index rebuild downtime.

Version notes
MySQL 8.0

ALTER TABLE ... ADD INDEX IF NOT EXISTS was introduced, making idempotent index creation possible.

Sources
Official documentation ↗

MySQL 8.0 — 1061 ER_DUP_KEYNAME

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors