Delete MySQL column index without knowing its name
database, hibernate, indexing, mysql, sql
Solution
You can select indexes for a table form information_schema:
SELECT DISTINCT INDEX_NAME, TABLE_NAME, TABLE_SCHEMA FROM information_schema.STATISTICS;
Problem
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.