How to Delete/Drop Columns from a Table in MySQL

08/03/2021

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.

#MySQL