How to delete an index in Grails with Liquibase

Question

I have a table generated by GORM (Grails Domain). It has foreign key / index that generated random characters like FKAC7AAF67162A158F. I need to remove that field that not needed anymore.

The problems, I have some servers that need to be updated. So I need to create a migration using Liquibase. But I have no idea how to remove that index manualy if the index are in random name (each server my have different name).

is it possible to drop an index of something without knowing its name ?

Answer 1

According to the MySQL Manual...

SHOW INDEX FROM mydb.mytable;

will return information about the mytable. It returns several fields with info about the table and its index, including a Column_name and key_name fields. You can probably sort out which one you need.

After that, you should be able to execute this:

DROP INDEX index_name ON tbl_name

And boom, no more index.

Answer 2

You can remove the index using the database-migration plugin (liquibase). It requires that you know the index name, but that name should be in one of your previous migrations.

// older index added in a previous release  
changeSet(author: "frank", id: "1354228052849-1") {
    createIndex(indexName: "FKAC7AAF67162A158F", tableName: "answer_option") {
        column(name: "question_id")
    }
}

Create a new migration to remove the index.

changeSet(author: "[email protected]", id: "1381257863746-1") {
    dropIndex(indexName: "FKAC7AAF67162A158F", tableName: "answer_option")
}