For optimal performance with DML statements, InnoDB requires an
index to exist on foreign
key columns, so that
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
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 '
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
NOT NULL. When
you drop such an index, InnoDB automatically copies the table and
rebuilds the index using a different
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 2.3, “Implementation Details of Fast Index Creation”.
Previously, an attempt to drop an implicit clustered index (the
UNIQUE NOT NULL index) failed if the
table did not contain a
ERROR 42000: This table type requires a primary key