Cannot drop index: needed in a foreign key constraint (MySQL 1553)
ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraintShort answer
InnoDB raises error 1553 when you try to drop the last index that can enforce a foreign key. Look up the constraint with SHOW CREATE TABLE, then either create another index that starts with the same columns before dropping the old one, or drop the foreign key first and then the index.
Fix it fast
1Find out which cause you have
Print the table definition. The CONSTRAINT lines show which foreign key uses which columns, and the KEY lines show your indexes.
SHOW CREATE TABLE child\G2Match what you see, then jump to its fix
- 1
SHOW CREATE TABLE shows a CONSTRAINT ... FOREIGN KEY (col) on the same column as the index you dropThe index is the only one on the foreign key columns of this tableFix - 2
The index is on a parent table, and a child table has a foreign key that points to those columnsAnother table's foreign key references the index you are droppingFix
- 1
What does the error look like?
ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraintCannot drop index: needed in a foreign key constraint
Cause 1 of 2
The index is the only one on the foreign key columns of this table
1Check
MySQL needs an index where the foreign key columns come first, in the same order. If you did not create one, MySQL created one for you.
SHOW INDEX FROM child;2Fix
If you want to replace the index, create the new one first. The manual says dropping is allowed when another index can still enforce the constraint. If you want to drop the index because the foreign key goes away, drop the foreign key first.
-- keep the constraint, swap the index
ALTER TABLE child ADD INDEX idx_parent_id (parent_id);
ALTER TABLE child DROP INDEX old_index_name;
-- or remove the constraint, then the index
ALTER TABLE child DROP FOREIGN KEY child_ibfk_1;
ALTER TABLE child DROP INDEX old_index_name;3Verify
SHOW CREATE TABLE no longer lists the old index, and the constraint is either still there with the new index or gone.
SHOW CREATE TABLE child\GConfirmed by dev.mysql.com
Cause 2 of 2
Another table's foreign key references the index you are dropping
1Check
A referenced key needs an index with the referenced columns first in the same order. Find the child constraints that name this table.
SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE referenced_table_name = 'parent' AND constraint_schema = DATABASE();2Fix
Drop the foreign key on the child table before dropping the index on the parent, as the manual requires. Add the constraint again later against a different index if you still need it.
ALTER TABLE child DROP FOREIGN KEY child_ibfk_1;
ALTER TABLE parent DROP INDEX old_index_name;3Verify
Both statements succeed. Check the child table for the missing constraint if you wanted to keep it.
SHOW CREATE TABLE child\GConfirmed by dev.mysql.com
How do I stop it coming back?
- Name foreign keys and their indexes yourself, so SHOW CREATE TABLE is easy to read.
- In a migration, create the replacement index before the DROP INDEX step.
- Do not turn off foreign_key_checks to get around this error: the manual says the foreign key must still be removed before the index.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to delete a row because other rows point to it
SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails
Error 1451 means at least one child row still references the parent row you are deleting or changing.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix