MySQL DROP Column

Overview

To drop a column, we can use the ALTER TABLE with the DROP COLUMN clause that specifies the column to be removed.

Syntax

The general syntax for the statement is a follows:

ALTER TABLE <table_name> DROP COLUMN <existing_column_name>

Examples

In this example, we will remove the column named District from the City table.

mysql> ALTER TABLE City DROP COLUMN District;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> DESC City;
+————-+———-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+———-+——+—–+———+—————-+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| Population | int | NO | | 0 | |
+————-+———-+——+—–+———+—————-+
4 rows in set (0.00 sec)

Notice that the column is not displayed in the DESCRIBE statement output.

MySQL Drop column Example

Common Errors

Trying to drop non-existent column from the table.

mysql> ALTER TABLE City DROP COLUMN District;
ERROR 1091 (42000): Can’t DROP ‘District’; check that column/key exists

Trying to drop a column that is referenced in a foreign key constraint.

The following drop column statement violates a foreign key constraint. This leads to an error.

mysql> ALTER TABLE Country DROP COLUMN Code;
ERROR 1829 (HY000): Cannot drop column ‘Code’: needed in a foreign key constraint ‘city_ibfk_1’ of table ‘city’

—