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 sometimes implicitly creates or drops such indexes as a side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

When you DROP an index, InnoDB checks that this operation does not compromise referential integrity checking. That is, it is OK to drop the index if it is not used for checking foreign key constraints, or if there is another index that can also be used for that purpose. InnoDB prevents 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 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 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

A similar change in error reporting applies to an attempt to drop the primary key index. InnoDB ensures that there is a primary key index for every table, even if you do not declare an explicit PRIMARY KEY. In such cases, InnoDB creates an implicit clustered index using the first columns of the table that are 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) fails 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 statement to drop such an index generate this error:

ERROR 42000: This table type requires a primary key

Beginning with MySQL 5.1.29 when using the InnoDB Plugin, attempts to drop such an index succeed. Behind the scenes, InnoDB copies the table, rebuilding the index using a different UNIQUE NOT NULL group of columns or a system-generated key. Since this operation changes the primary key, it uses the slow method of copying the table and re-creating the index, rather than the Fast Index Creation technique from Section 13.7.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 must first use the ALTER TABLE statement to remove foreign key references from or to other tables. Because MySQL does not support dropping or creating FOREIGN KEY constraints, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table is copied, rather than using “Fast Index Creation”.