Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Cannot drop index: needed in a foreign key constraint (MySQL 1553)

ErrorERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraint

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1SHOW 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. 2The 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

What does the error look like?

  • ERROR 1553 (HY000): Cannot drop index 'index_name': needed in a foreign key constraint
  • Cannot 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\G

Confirmed by dev.mysql.com

Did this fix it?

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\G

Confirmed by dev.mysql.com

Did this fix it?

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:

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.