For efficiency, InnoDB requires an index to exist on foreign key columns so that UPDATE and DELETE operations on a “parent” table can easily check for the existence or non-existence of corresponding rows in the “child” table. To ensure that there is an appropriate index for such checks, MySQL will sometimes implicitly create or drop such indexes as a side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

When you explicitly DROP an index, InnoDB will check that an index suitable for referential integrity checking will still exist following the DROP of the index. InnoDB will prevent you from dropping the last usable index for enforcing any given referential constraint. Users have been confused by this behavior, as reported in MySQL Bug#21395.

In releases prior to InnoDB storage engine 1.0.2, attempts to drop the only usable index would result in an error message such as

ERROR 1025 (HY000): Error on rename of './db2/#sql-18eb_3'
to './db2/foo'(errno: 150)

Beginning with InnoDB storage engine 1.0.2, this error condition is reported with a more friendly message:

ERROR 1553 (HY000): Cannot drop index 'fooIdx':
needed in a foreign key constraint

As a related matter, because all user data in InnoDB is maintained in the so-called “clustered index” (or primary key index), InnoDB ensures that there is such an index for every table, even if the user does not declare an explicit PRIMARY KEY. In such cases, InnoDB will create an implicit clustered index using the first columns of the table that have been declared UNIQUE and NOT NULL.

When the InnoDB storage engine is used with a MySQL version earlier than 5.1.29, an attempt to drop an implicit clustered index (the first UNIQUE NOT NULL index) will fail if the table does not contain a PRIMARY KEY. This has been reported as MySQL Bug#31233. Attempts to use the DROP INDEX or ALTER TABLE command to drop such an index will generate this error:

ERROR 42000: This table type requires a primary key

Beginning with MySQL 5.1.29 when using the InnoDB storage engine, attempts to drop such an index will copy the table, rebuilding the index using a different UNIQUE NOT NULL group of columns or a system-generated key. Note that all indexes will be re-created by copying the table, as described in Section 2.3, “Implementation Details of Fast Index Creation”.

In those versions of MySQL that are affected by this bug, one way to change an index of this type is to create a new table and copy the data into it using INSERT INTO newtable SELECT * FROM oldtable, and then DROP the old table and rename the new table.

However, if there are existing tables with references to the table whose index you are dropping, you will first need to use the ALTER TABLE command to remove foreign key references from or to other tables. Unfortunately, MySQL does not support dropping or creating FOREIGN KEY constraints, even though dropping a constraint would be trivial. Therefore, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table will be copied, rather than using “Fast Index Creation”.