[FIXED] Delete MySQL column index without knowing its name

Issue

I have an application which uses Hibernate to support Oracle and MySQL databases. After an update I have to manually delete some columns with indexes/constraints on it. These indexes have Hibernate generated random names.

In Oracle I can do this:

ALTER TABLE table_name DROP (column_name) CASCADE CONSTRAINTS;

Unfortunately this isn't possible for MySQL. Is there a possibility to do something like this

DROP INDEX (SELECT Key_name FROM (SHOW INDEX FROM table_name WHERE Column_name = 'column_name')) ON table_name;

before I drop the column?

EDIT: This should work without user interaction in a SQL script.

Solution

You can select indexes for a table form information_schema:

SELECT DISTINCT INDEX_NAME, TABLE_NAME, TABLE_SCHEMA FROM information_schema.STATISTICS;

Answered By - user1209304
Answer Checked By - Marie Seifert (JavaFixing Admin)