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.
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’
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: