EC
1560
MySQLERRORNotableDDLHIGH confidence

Cannot drop index needed for a foreign key constraint

Production Risk

Medium — the DROP INDEX fails; the foreign key relationship is preserved.

What this means

ER_DROP_INDEX_FK (1560, SQLSTATE HY000) is raised when attempting to DROP an index that is being used to enforce a foreign key constraint.

Why it happens
  1. 1DROP INDEX on an index that supports a FOREIGN KEY constraint
  2. 2ALTER TABLE DROP INDEX where the index is required by a foreign key
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE orders DROP INDEX idx_customer_id;
-- idx_customer_id supports a FOREIGN KEY to customers.id

expected output

ERROR 1560 (HY000): Cannot drop index 'idx_customer_id': needed in a foreign key constraint

Fix 1

Drop the foreign key constraint before dropping the index

Drop the foreign key constraint before dropping the index
-- Find the constraint name:
SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'customer_id'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- Drop the foreign key first:
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Then drop the index:
ALTER TABLE orders DROP INDEX idx_customer_id;

Why this works

Foreign key constraints depend on their supporting index; the constraint must be dropped first.

Fix 2

Replace the index while keeping the foreign key

Replace the index while keeping the foreign key
-- Add a replacement index first, then drop the old one:
ALTER TABLE orders
  ADD INDEX idx_customer_id_new (customer_id),
  DROP INDEX idx_customer_id;
-- MySQL uses the new index for the foreign key

Why this works

Adding an equivalent index before dropping the original keeps the foreign key supported.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1560 ER_DROP_INDEX_FK

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors