📋

Description

MariaDB Error 1061, 'Duplicate key name', occurs when you attempt to create an index or a constraint (like a primary key, unique key, or foreign key) with a name that already exists within the same table. MariaDB requires all index and constraint names within a table to be unique, preventing the DDL statement from executing.

💬

Error Message

Duplicate key name '%s'

🔍

Known Causes

3 known causes

⚠️

Attempting to Re-create Existing Index

You tried to create an index or constraint with a name that already exists on the table, often when running a script multiple times without proper checks.

⚠️

Schema Migration Script Error

Automated schema migration tools or scripts might attempt to create an index or constraint that was already created in a previous step or version.

⚠️

Accidental Duplicate Definition in DDL

The SQL DDL statement contains two or more definitions for indexes or constraints that share the exact same name.

🛠️

Solutions

3 solutions available

1. Rename Existing Index/Constraint easy

Find the duplicate index/constraint name and rename the one causing the conflict.

1

Identify the duplicate name reported in the error message. Let's assume the duplicate name is 'my_duplicate_index'.

2

Connect to your MariaDB instance.

3

Query the information schema to find all indexes and their names for the relevant table. Replace 'your_database_name' and 'your_table_name' with your actual database and table names. You can omit the table name to see all indexes in a database.

SELECT INDEX_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND INDEX_NAME = 'my_duplicate_index';

-- To see all indexes in a database:
-- SELECT INDEX_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name';

4

Once you've identified which index is the duplicate (you might see it associated with different tables or even the same table if it was attempted to be created twice), use the ALTER TABLE statement to rename the conflicting index to a unique name. Replace 'your_table_name', 'old_index_name', and 'new_unique_index_name' accordingly.

ALTER TABLE your_table_name RENAME INDEX old_index_name TO new_unique_index_name;

2. Drop the Conflicting Index/Constraint easy

Remove the index or constraint that is causing the duplicate name error.

1

Identify the duplicate index/constraint name from the error message. Let's assume it's 'my_duplicate_index'.

2

Connect to your MariaDB instance.

3

Use the ALTER TABLE statement to drop the index or constraint. If it's a PRIMARY KEY or UNIQUE constraint, the syntax will be slightly different. Replace 'your_table_name' and 'my_duplicate_index' with your actual table and the index/constraint name.

-- For a regular index:
ALTER TABLE your_table_name DROP INDEX my_duplicate_index;

-- For a PRIMARY KEY (use with caution, ensure it's not the only PK):
-- ALTER TABLE your_table_name DROP PRIMARY KEY;

-- For a UNIQUE constraint:
-- ALTER TABLE your_table_name DROP INDEX unique_constraint_name;

4

Re-create the index or constraint with a unique name if it was intended to exist.

-- Example for re-creating an index:
ALTER TABLE your_table_name ADD INDEX new_unique_index_name (column1, column2);

3. Review and Clean Up Schema Creation Scripts medium

Examine your SQL scripts to find where the index or constraint is being created multiple times.

1

Locate the SQL scripts or migration files that are responsible for creating your database schema, tables, indexes, and constraints.

2

Carefully review these scripts for any duplicate `CREATE INDEX`, `ALTER TABLE ... ADD INDEX`, `ALTER TABLE ... ADD CONSTRAINT`, or `ALTER TABLE ... ADD PRIMARY KEY` statements that use the same index or constraint name for the same table.

3

Remove or comment out the redundant `CREATE` or `ADD` statements. Ensure that each index or constraint is defined only once.

--- Example of redundant code to remove ---
-- CREATE INDEX my_duplicate_index ON your_table_name (column1);
-- ALTER TABLE your_table_name ADD INDEX my_duplicate_index (column1);

4

Apply the corrected script to your MariaDB instance. It's often best to do this in a development or staging environment first.

🔗

Related Errors

5 related errors

MariaDB Error 1061: Duplicate Index or Constraint Name