For efficiency, InnoDB requires an index to exist on foreign key
columns so that
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
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
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 Plugin 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 '
Beginning with InnoDB Plugin 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
When the InnoDB Plugin is used with a MySQL version earlier
than 5.1.29, an attempt to drop an implicit clustered index (the
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 Plugin,
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”.
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
, and then
newtable SELECT * FROM
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
FOREIGN KEY constraints, even though dropping a
constraint would be trivial. Therefore, if you use
to add or remove a
REFERENCES constraint, the child table will
be copied, rather than using “Fast Index Creation”.