14.2.5.9. Better Error Handling when Dropping Indexes

For optimal performance with DML statements, InnoDB requires an index to exist on foreign key columns, so that UPDATE and DELETE operations on a parent table can easily check whether corresponding rows exist in the child table. MySQL creates or drops such indexes automatically when needed, as a side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

When you drop an index, InnoDB checks whether the index is not used for checking a foreign key constraint. It is still OK to drop the index if there is another index that can be used to enforce the same constraint. InnoDB prevents you from dropping the last index that can enforce a particular referential constraint.

The message that reports this error condition is:

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

This message is friendlier than the earlier message it replaces:

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

A similar change in error reporting applies to an attempt to drop the primary key index. For tables without an explicit PRIMARY KEY, InnoDB creates an implicit clustered index using the first columns of the table that are declared UNIQUE and NOT NULL. When you drop such an index, InnoDB automatically copies the table and rebuilds 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 5.5.6, "Implementation Details of Online DDL".

Previously, an attempt to drop an implicit clustered index (the first UNIQUE NOT NULL index) failed if the table did not contain a PRIMARY KEY:

ERROR 42000: This table type requires a primary key