MySQL DROP COLUMN: syntax and safety

Quick answer

ALTER TABLE users DROP COLUMN middle_name;

The COLUMN keyword is optional (DROP middle_name works), but include it. DROP on its own is ambiguous to read and easy to confuse with dropping an index.

Dropping multiple columns

One statement, one pass over the table:

ALTER TABLE users
  DROP COLUMN middle_name,
  DROP COLUMN nickname;

Do this rather than issuing separate ALTER TABLE statements. Each ALTER can rebuild the table, so two statements can mean two rebuilds. Combining them is one operation.

You can mix operations in the same statement:

ALTER TABLE users
  DROP COLUMN legacy_id,
  ADD COLUMN external_id VARCHAR(64),
  MODIFY COLUMN email VARCHAR(320) NOT NULL;

There is no IF EXISTS

This is the first thing people try, and it does not work:

ALTER TABLE users DROP COLUMN IF EXISTS nickname;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'IF EXISTS nickname' at line 1

MySQL supports IF EXISTS for DROP TABLE and DROP INDEX, but not for DROP COLUMN. PostgreSQL does, and MariaDB does, which is why the expectation is so common. MySQL does not.

Dropping a column that is not there gives:

ALTER TABLE users DROP COLUMN nope;

ERROR 1091 (42000): Can't DROP 'nope'; check that column/key exists

For an idempotent migration, check the catalog first:

SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'nickname';

Then branch in your migration tool. Inside a stored procedure you can build the statement conditionally:

SET @exists := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'nickname'
);
SET @sql := IF(@exists > 0, 'ALTER TABLE users DROP COLUMN nickname', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

That is verbose enough that most people let their migration tool handle it, which is the right instinct.

Dropping a column drops indexes that used it

This is the side effect worth knowing about, because nothing warns you.

Given a table with two indexes:

CREATE TABLE t (
  id int PRIMARY KEY,
  a int, b int, c int,
  INDEX idx_b (b),
  INDEX idx_bc (b, c)
);

ALTER TABLE t DROP COLUMN b;
SHOW INDEX FROM t;

Table  Key_name   Seq_in_index  Column_name
t      PRIMARY    1             id
t      idx_bc     1             c

Two things happened:

• idx_b is gone entirely. It indexed only b, so dropping b destroyed it.
• idx_bc survived, but is now an index on (c) alone. Its leading column was removed.

That second one is the dangerous one. The index still exists and still has its old name, so a casual SHOW INDEX looks fine. But an index on (b, c) and an index on (c) answer completely different questions. Queries that relied on the composite are now doing something else, and you will find out through a slow query rather than an error.

After dropping a column, review any composite index that included it.

If a column is the only column of a UNIQUE constraint, that constraint disappears with it, silently removing a data-integrity guarantee.

Foreign keys block the drop

CREATE TABLE child (id int PRIMARY KEY, pid int, FOREIGN KEY (pid) REFERENCES parent(id));
ALTER TABLE child DROP COLUMN pid;

ERROR 1828 (HY000): Cannot drop column 'pid': needed in a foreign key constraint 'child_ibfk_1'

Drop the constraint first, then the column:

ALTER TABLE child DROP FOREIGN KEY child_ibfk_1;
ALTER TABLE child DROP COLUMN pid;

Note DROP FOREIGN KEY takes the constraint name, not the column name. Find it with:

SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'child'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

Names like child_ibfk_1 are auto-generated and are not stable across environments. Do not hard-code them in a migration that has to run somewhere else.

You cannot drop every column

CREATE TABLE solo (only_col int);
ALTER TABLE solo DROP COLUMN only_col;

ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead

A table must have at least one column. The error tells you exactly what to do.

ALGORITHM=INSTANT

Since MySQL 8.0.29, InnoDB can drop a column instantly, without rebuilding the table:

ALTER TABLE t DROP COLUMN junk, ALGORITHM=INSTANT;

On a large table this is the difference between milliseconds and hours. It works by marking the column as dropped in metadata rather than rewriting every row.

Specifying ALGORITHM=INSTANT explicitly is worth doing even though MySQL will often pick it anyway, because if the operation cannot be instant, you get an error instead of an unexpected multi-hour table rebuild on production:

ALTER TABLE alg MODIFY COLUMN v varchar(500), ALGORITHM=INSTANT;

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild
the table to change column type. Try ALGORITHM=COPY/INPLACE.

That is a much better outcome than discovering it during the maintenance window, and the Reason: tells you which part of your statement was the problem.

Caveats worth knowing:

• Instant drops leave the old data in place. Row size does not shrink until the table is rebuilt, so you reclaim no space.
• There is a limit on how many instant column changes a table can accumulate before a rebuild is forced.
• It is InnoDB-only.

The other algorithms:

Dropping a column is not reversible

There is no undo. The data is gone, and on a large table restoring it means a full restore or a rebuild from backup.

Two habits worth having.

Stop using the column before you drop it. Deploy code that no longer reads or writes it, let it run long enough to be sure, then drop. A column dropped while something still selects it produces ERROR 1054: Unknown column, and that error will come from whichever service you forgot about.

Consider renaming first. Renaming is instant and reversible:

ALTER TABLE users RENAME COLUMN nickname TO nickname_deprecated;

Anything still referencing it breaks immediately and loudly, in a way you can undo in seconds. Drop it for real once a full deploy cycle has passed.

RENAME COLUMN needs MySQL 8.0. On 5.7 you need CHANGE COLUMN with the full type definition repeated, which is its own hazard: get the type wrong and you have silently altered the column.

Common problems

ERROR 1091: Can't DROP 'x'. The column does not exist, or you spelled it differently. Column names are case-insensitive on most platforms but the check is against the real name.

ERROR 1064 near IF EXISTS. MySQL has no IF EXISTS for columns. Check information_schema instead.

ERROR 1828: needed in a foreign key constraint. Drop the constraint first.

ERROR 1090: You can't delete all columns. Use DROP TABLE.

ERROR 1054: Unknown column after a deploy. Something still references the dropped column. This is the one that causes outages, and it is why you stop using a column before dropping it.

The ALTER is taking hours. It is rebuilding the table. Cancel it, add ALGORITHM=INSTANT, and see whether it is eligible. If not, use an online schema change tool such as pt-online-schema-change or gh-ost.

Quick reference