How to Delete/Drop Columns from a Table in MySQL

Contents
In this article, you will learn how to delete or drop columns from a table in MySQL.
Deleting/Dropping columns from a table in MySQL
In MySQL, you may need to delete or drop columns from an existing table to remove unnecessary or redundant data.
Syntax for deleting columns from a table in MySQL
The syntax for deleting columns from a table in MySQL is as follows:
ALTER TABLE table_name
DROP COLUMN column_name;
- “table_name” is the name of the table you want to delete a column from.
- “column_name” is the name of the column you want to delete.
Example of deleting columns from a table in MySQL
Let’s look at an example of how to delete the “phone_number” column from the “employees” table:
ALTER TABLE employees
DROP COLUMN phone_number;
This command removes the “phone_number” column from the “employees” table.
Dropping multiple columns in MySQL
If you need to remove multiple columns from a table in MySQL, you can use the “DROP COLUMN” clause multiple times in the same ALTER TABLE statement. Here’s an example of how to remove the “phone_number” and “hire_date” columns from the “employees” table:
ALTER TABLE employees
DROP COLUMN phone_number,
DROP COLUMN hire_date;
This command removes both the “phone_number” and “hire_date” columns from the “employees” table.
Deleting columns with constraints in MySQL
If a column has constraints (e.g. PRIMARY KEY, UNIQUE, FOREIGN KEY), you must first drop the constraint before you can delete the column. Here’s an example of how to delete the “department_id” column, which is a FOREIGN KEY constraint:
ALTER TABLE employees
DROP FOREIGN KEY fk_department_id,
DROP COLUMN department_id;
This command drops the FOREIGN KEY constraint named “fk_department_id” and then removes the “department_id” column from the “employees” table.