<p>The ER_DUP_INDEX error occurs when a CREATE or ALTER TABLE statement defines an index that duplicates an existing one on the same table.</p>
<p>MySQL Error 1831: ER_DUP_INDEX signals that your CREATE or ALTER TABLE statement tries to add an index identical to one already present. Drop or rename the duplicate index to resolve the issue.</p>
Duplicate index '%s' defined on the table '%s.%s'. This
Error 1831 appears when MySQL detects that a new index duplicates the column list and index type of an existing index on the same table.
The server blocks the operation to avoid redundant structures that waste storage and slow writes. From MySQL 8.0, duplicate indexes are deprecated and will be disallowed in future releases, so correcting them is critical.
The error arises during CREATE TABLE, ALTER TABLE ADD INDEX, or implicit index creation by engine-specific constraints such as UNIQUE or PRIMARY KEY definitions.
It can also surface while restoring dumps or running migration tools if schema scripts unintentionally repeat index definitions.
Most cases stem from developers unintentionally adding the same index twice, often due to naming confusion or automated migration scripts.
Sometimes application frameworks generate default indexes that clash with manually added ones, especially on composite keys.
Leaving duplicate indexes wastes disk space, increases maintenance I/O, and degrades INSERT, UPDATE, and DELETE performance.
Future MySQL versions will reject duplicate indexes entirely, so resolving them now ensures forward compatibility.
The primary fix is to drop or rename the duplicate index, then rerun the DDL statement.
If both indexes are required but differ subtly, alter one so the column order or index type (BTREE vs HASH) is unique.
Maintain a single source of truth for schema migrations and review generated SQL before execution.
Use descriptive index names and enforce code reviews or automated linting to catch duplicate definitions early.
Galaxy’s schema-aware autocompletion warns you when an index already exists on the chosen column set, preventing ER_DUP_INDEX before it reaches production.
Teams can endorse vetted DDL scripts in Galaxy Collections, ensuring everyone reuses approved index definitions and avoids accidental duplication.
Creating a new index on the same columns, in the same order, duplicates an existing index.
ORMs like Hibernate or Rails may auto-create indexes that overlap with manually added ones.
Parallel branches can each add the same index, leading to duplication when merged.
Declaring a UNIQUE or FOREIGN KEY that auto-generates an index identical to one already present triggers the error.
Occurs when two indexes share the same name even if their column lists differ.
Raised when a table exceeds the maximum number of allowed indexes.
Triggered when an index references a column that does not exist in the table.
No. MySQL checks the column list and type, not just the name. You must alter the structure or drop the duplicate.
No. Future releases will simply refuse to create them. Manual cleanup is required.
Query INFORMATION_SCHEMA.STATISTICS or use tooling like Galaxy, which flags identical column sets.
Not unless the column lists match exactly. Indexes must be identical in columns and order to be considered duplicates.
.avif)
.avif){kind=logo}
