MySQL Alter Table - Drop Column

Last updated: October 19, 2026Oct 19, 2026 5 min read

Columns that are no longer used, like a fax number nobody fills in, make a table harder to understand and waste space. ALTER TABLE … DROP COLUMN removes a column and all the data in it. This tutorial shows how, and what to check first so nothing else breaks.

1. Syntax

ALTER TABLE table_name DROP [COLUMN] column_name [, DROP [COLUMN] column_name ...];

2. Dropping a Column

ALTER TABLE students DROP COLUMN pager;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

SHOW COLUMNS FROM students;

Result: 6 rows

3. Dropping Several Columns

Combine several DROP clauses in one statement so the table is changed only once. First let's add two throw-away columns:

ALTER TABLE students ADD COLUMN temp1 INT, ADD COLUMN temp2 INT;
ALTER TABLE students DROP COLUMN temp1, DROP COLUMN temp2;
SHOW COLUMNS FROM students LIKE 'temp%';

Result: Empty set (0 rows)

4. Columns Other Objects Depend On

Before dropping a column, find out what uses it. Each kind of dependency behaves differently.

Indexes

If the column is part of an index, MySQL removes it from the index (or drops the index if it was the only column):

ALTER TABLE students ADD COLUMN phone VARCHAR(15), ADD INDEX idx_phone (phone);
ALTER TABLE students DROP COLUMN phone;
SHOW INDEX FROM students;   -- idx_phone is gone

Result: 4 rows

Foreign keys

A column used in a foreign key can't be dropped until the constraint is dropped:

ALTER TABLE students DROP COLUMN course_id;

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

ALTER TABLE students DROP FOREIGN KEY fk_course;
ALTER TABLE students DROP COLUMN course_id;
SHOW COLUMNS FROM students LIKE 'course%';

Result: Empty set (0 rows)

CHECK constraints

A CHECK constraint that refers only to the dropped column is removed with it. If it refers to several columns, the drop fails until you drop the constraint:

ALTER TABLE students DROP COLUMN age;
SELECT constraint_name FROM information_schema.check_constraints WHERE constraint_schema = 'college';

Result: Empty set (0 rows)

Views

Views are not checked. The view keeps the old column name and breaks:

ALTER TABLE students DROP COLUMN fax;
SELECT * FROM student_contacts;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
ERROR 1356 (HY000): View 'college.student_contacts' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

CREATE OR REPLACE VIEW student_contacts AS SELECT name, email FROM students;
SELECT * FROM student_contacts;

Result: 3 rows

5. Generated Columns

A column that a generated column is calculated from can't be dropped while the generated column exists:

ALTER TABLE students ADD COLUMN email_domain VARCHAR(40) AS (SUBSTRING_INDEX(email, '@', -1));
ALTER TABLE students DROP COLUMN email;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
ERROR 3108 (HY000): Column 'email' has a generated column dependency.

6. You Can't Drop the Last Column

CREATE TABLE tiny (only_col INT);
ALTER TABLE tiny DROP COLUMN only_col;

Query OK, 0 rows affected
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead

Use DROP TABLE to remove the table instead.

7. Dropping Only If the Column Exists

MySQL has no DROP COLUMN IF EXISTS. Dropping a missing column is an error:

ALTER TABLE students DROP COLUMN fax;

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

Check information_schema.columns first in scripts, or let a migration tool (Flyway, Liquibase) record what has already run.

8. Safe Drop Checklist

1. Search application code and reports for the column name.
2. Check views, routines and triggers: SELECT table_name FROM information_schema.views WHERE view_definition LIKE '%fax%';
3. Check foreign keys and generated columns that use it.
4. Back up the data, for example CREATE TABLE students_fax_backup AS SELECT id, fax FROM students;
5. Optionally make it invisible first (ALTER TABLE t ALTER COLUMN c SET INVISIBLE, 8.0.23+) so SELECT * stops returning it, and drop it later.
6. On large tables, test the time it takes; since 8.0.29 many drops are INSTANT, older versions rebuild the table.

9. Practice Exercises

ALTER TABLE students DROP COLUMN email_domain, DROP COLUMN email;
SHOW COLUMNS FROM students;

SELECT table_name FROM information_schema.columns
WHERE table_schema = 'college' AND column_name = 'title';

10. Summary

• ALTER TABLE t DROP COLUMN c; removes the column and its data permanently.
• Drop several columns in one statement with comma-separated DROP clauses.
• Drop foreign keys and dependent generated columns first; indexes and single-column CHECKs adjust automatically.
• Views are not checked and will break; recreate them.
• No IF EXISTS: check information_schema, and back up before dropping.