How to Fix MySQL Error 1091: Can't DROP — Check That Column or Key Exists

ERROR 1091 (42000): Can't DROP '<name>'; check that column/key exists is raised when an ALTER TABLE statement attempts to drop a column or index that does not exist on the target table. The error symbol is ER_CANT_DROP_FIELD_OR_KEY.

Impact

The ALTER TABLE statement fails immediately and no schema change is made. The table remains unchanged. This error occurs at DDL execution time, not during data reads or writes, so existing data is not affected. Any transaction containing the failing ALTER TABLE is rolled back (for storage engines that support transactional DDL), but because MySQL's InnoDB treats most DDL as implicitly committed, the practical effect is simply that the statement is rejected.

Developers most commonly encounter this during database migrations. Migration frameworks such as Flyway, Liquibase, Rails ActiveRecord, and Django will abort the migration and report the error, leaving the migration in a failed or partially-applied state depending on whether other statements in the script already executed.

Common Causes

Column or index name typo. The name passed to DROP COLUMN or DROP INDEX does not exactly match the name stored in the schema. MySQL identifiers are case-insensitive on most platforms, but trailing spaces or invisible Unicode characters can cause mismatches.
Column or index was already dropped. A previous migration or manual change already removed the column or index. Running the same migration script a second time, or running scripts out of order, reproduces the error.
Wrong table targeted. The ALTER TABLE references the right column name but the wrong table — perhaps because the database or table name differs between environments (production vs. staging).
Migration generated against a different schema version. An ORM auto-generated migration based on a model that was already partially applied, leading to a DROP for a column that no longer exists.
Using DROP INDEX instead of DROP FOREIGN KEY (or vice versa). Foreign key constraints have their own constraint name. Attempting to DROP INDEX fk_name when the constraint is a foreign key named fk_name will fail; the correct syntax is DROP FOREIGN KEY fk_name.

Troubleshooting and Resolution Steps

Verify the column exists on the table.
```
DESCRIBE my_table;
-- or
SHOW COLUMNS FROM my_table;
```
If the column is absent, it was already dropped or never existed under that name.
Verify the index exists on the table.
```
SHOW INDEX FROM my_table;
```
The Key_name column lists all index names. Cross-check it against the name in your DROP INDEX clause.

Query information_schema for a programmatic check.

-- Check for a column
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME   = 'my_table'
  AND COLUMN_NAME  = 'the_column';

-- Check for an index
SELECT INDEX_NAME
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME   = 'my_table'
  AND INDEX_NAME   = 'the_index';

An empty result set confirms the object does not exist.

Use IF EXISTS to make the statement idempotent (MySQL 8.0+ for columns; available for indexes in older versions).
```
-- Safe column drop (MySQL 8.0.29+)
ALTER TABLE my_table DROP COLUMN IF EXISTS old_column;

-- Safe index drop (supported since MySQL 5.x)
ALTER TABLE my_table DROP INDEX IF EXISTS old_index;
```
IF EXISTS silences the error when the object is absent, which is useful in migration scripts that may be replayed.

Distinguish between an index and a foreign key constraint.

-- List foreign key constraints
SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA    = 'your_database'
  AND TABLE_NAME      = 'my_table'
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

If your intended target is a foreign key, drop it with:

ALTER TABLE my_table DROP FOREIGN KEY fk_constraint_name;

Note that dropping a foreign key constraint does not automatically drop the underlying index. You may need a separate DROP INDEX statement afterward.

Fix out-of-order or duplicate migrations. If migrations are being rerun, add guard logic or use your migration tool's checksum/version tracking. In a manual script, wrap the statement in a stored procedure or use IF EXISTS:

SET @db   = 'your_database';
SET @tbl  = 'my_table';
SET @col  = 'old_column';

SET @sql = IF(
  EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db AND TABLE_NAME = @tbl AND COLUMN_NAME = @col
  ),
  CONCAT('ALTER TABLE `', @tbl, '` DROP COLUMN `', @col, '`'),
  'SELECT "Column does not exist, skipping."'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Additional Information

The SQLSTATE for this error is 42000 (Syntax Error or Access Rule Violation), which is the same class used for many DDL-related errors.
DROP INDEX IF EXISTS has been available since MySQL 5.7 for most storage engines. DROP COLUMN IF EXISTS was added in MySQL 8.0.29.
On replicated setups, running ALTER TABLE without IF EXISTS on a replica that diverged from the primary can cause replication to break with error 1091. Use IF EXISTS in migration scripts applied to replication topologies.
ORM migration tools often track applied migrations in a metadata table (e.g., schema_migrations, flyway_schema_history). If a migration was marked complete but the DDL failed partway through, the schema and the migration log are out of sync. Check both before re-running.
Related errors: ERROR 1060 (42S21) ER_DUP_FIELDNAME (adding a column that already exists), ERROR 1072 (42000) ER_KEY_COLUMN_DOES_NOT_EXIST (index references a non-existent column).

Frequently Asked Questions

Why does this error say 42000 (syntax error) when there is nothing wrong with my SQL syntax? The SQLSTATE 42000 covers a broad category of errors including "access rule violation" — not just literal syntax problems. MySQL uses it here because the schema rule (the column or key must exist before it can be dropped) was violated, even though the SQL statement itself is syntactically valid.

Can I drop a column and an index in the same ALTER TABLE statement? Yes. MySQL allows multiple operations in a single ALTER TABLE clause:

ALTER TABLE my_table
  DROP COLUMN old_column,
  DROP INDEX  old_index;

If either name does not exist, the entire statement fails. Use IF EXISTS on each clause to make it resilient.

What is the difference between DROP INDEX and DROP KEY in MySQL? They are synonyms in MySQL. DROP KEY and DROP INDEX are interchangeable inside an ALTER TABLE statement. Both will fail with error 1091 if the specified index name does not exist.

My migration tool reports error 1091 even though the column was never dropped manually. What should I check? Verify the migration execution order and whether the table was created with the expected columns. If using an ORM, regenerate or diff the migration against the current database state. Also check for case-sensitivity differences in column names between the migration script and what SHOW COLUMNS reports.