📋

Description

MySQL Error 1061, 'Duplicate key name', occurs when an attempt is made to create an index or constraint (such as PRIMARY KEY, UNIQUE, or FOREIGN KEY) using a name that is already in use by another index or constraint within the same table. This prevents the DDL operation from completing successfully.

💬

Error Message

Duplicate key name '%s'

🔍

Known Causes

3 known causes

⚠️

Existing Index or Constraint Name

You tried to add an index or constraint to a table using a name that is already assigned to another index or constraint within the same table.

⚠️

Non-Idempotent SQL Script

A SQL script containing `CREATE INDEX` or `ALTER TABLE ADD CONSTRAINT` statements was executed multiple times, attempting to create the same named object repeatedly.

⚠️

Schema Migration Conflict

During a database schema migration or synchronization, an index or constraint was defined with a name that already exists in the target database's table.

🛠️

Solutions

4 solutions available

1. Use Different Index Name easy

Choose a unique name for your index

1

Check existing indexes

SHOW INDEX FROM your_table;

2

Create index with unique name

-- Instead of:
-- CREATE INDEX idx_email ON users (email);

-- Use unique name:
CREATE INDEX idx_users_email ON users (email);

2. Drop Existing Index First easy

Remove old index before creating new one

1

Drop existing index

DROP INDEX idx_email ON users;

2

Create new index

CREATE INDEX idx_email ON users (email);

3

Or use ALTER TABLE

ALTER TABLE users DROP INDEX idx_email, ADD INDEX idx_email (email);

3. Use IF NOT EXISTS (MySQL 8.0+) easy

Create only if doesn't exist

1

Check MySQL version

SELECT VERSION();

2

Use IF NOT EXISTS (MySQL 8.0.29+)

CREATE INDEX IF NOT EXISTS idx_email ON users (email);

4. Handle Duplicate Key in Foreign Key medium

Foreign key constraint names must be unique

1

Check existing constraints

SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

2

Use unique constraint name

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id);

🔗

Related Errors

5 related errors

MySQL Error 1061: Duplicate Key Name