How to Rename an Index in MySQL

How do I rename an Index in MySQL

I answered this question in 2009. At that time there was no syntax in MySQL to rename an index.

Since then, MySQL 5.7 introduced an ALTER TABLE RENAME INDEX syntax.

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html says in part:

• RENAME INDEX old_index_name TO new_index_name renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged. old_index_name must be the name of an existing index in the table that is not dropped by the same ALTER TABLE statement. new_index_name is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can be PRIMARY.

Earlier versions of MySQL, e.g. 5.6 and earlier, support no syntax in ALTER TABLE to rename an index (or key, which is a synonym).

The only solution was to ALTER TABLE DROP KEY oldkeyname, ADD KEY newkeyname (...).

There is no ALTER INDEX command in MySQL. You can only DROP INDEX and then CREATE INDEX with the new name.

---

Regarding your update above: perhaps the documentation isn't precise enough. Regardless, there's no SQL syntax to rename an index.

An index is a data structure that can be rebuilt from the data (in fact it's recommended to rebuild indexes periodically with OPTIMIZE TABLE). It takes some time, but it's a commonplace operation. Indexes data structures are separate from table data, so adding or dropping an index shouldn't need to touch the table data, as the documentation says.

Regarding the .frm file, MySQL does not support editing the .frm file. I wouldn't do it for any reason. You are 100% guaranteed to corrupt your table and make it unusable.

---

How can I rename all the indexes in MySQL

MySQL supports no syntax in ALTER TABLE to rename an index (or key, which is a synonym).

You can ALTER TABLE DROP KEY and ALTER TABLE ADD KEY.

There is no ALTER INDEX command in MySQL. You can only DROP INDEX and then CREATE INDEX with the new name.

Renaming a Unique Key Name MySQL

You have to drop it and then recreate it again with the new name:

alter table yourTableName drop index ExtendedOrderID;

create unique index OrderLineItemID on yourTableName (OrderLineItemID);

Is it possible to rename an index using a rails migration?

You can execute arbitrary SQL in your migrations as well.

We have some helper methods that add foreign keys to our tables:

def add_foreign_key(from_table, from_column, to_table)
  constraint_name = "fk_#{from_table}_#{from_column}"

  execute %{alter table #{from_table}
            add constraint #{constraint_name}
            foreign key (#{from_column})
            references #{to_table}(id)
           }
  end

You can use any SQL your database supports.

How to rename an index in SQLite?

SQLite's ALTER TABLE does not support this. Just recreate it:

DROP INDEX old_index;
CREATE INDEX new_index ON demo([...]);

You can get the index definition from the schema:

SELECT sql FROM sqlite_master WHERE type = 'index' AND name = 'old_index';

But there is no mechanism to modify and execute the result from inside SQLite.

---

Related Topics