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
, and then
newtable
SELECT * FROM
oldtable
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”.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .