MySQL DROP COLUMN

Sometimes you may need to delete column from database table in MySQL. You can easily do this using MySQL DROP COLUMN command. Here’s how to drop column from table in MySQL.

Table of Contents

How to Drop Column from Table in MySQL

Here are the steps to drop column from table in MySQL. We will use MySQL DROP COLUMN query to remove existing columns from table.

Here’s the syntax of DROP COLUMN statement:

ALTER TABLE table_name
DROP COLUMN column_name;

In the above statement, you need to mention the table whose column you want to drop, in ALTER TABLE clause. Then specify the column name in DROP COLUMN clause.

You can use only DROP, instead of using DROP COLUMN above.

If you want to drop multiple columns from table, mention them in separate DROP COLUMN clauses, in same statement.

ALTER TABLE table_name
DROP COLUMN column1,
DROP COLUMN column2,
DROP COLUMN column3;

When you remove a column from table, all stored procedures, views and triggers that refer to this column become invalid. You will need to manually update them to get them working again. You can also drop column with Index but the index will become invalid after removal of column.

Bonus Read : MySQL DROP TABLE

MySQL DROP COLUMN Examples

Let’s say you have the following table orders

mysql> create table orders(order_date date, 
       sale int, 
       product_id int, 
       category varchar(255));

Let’s say you want to DROP COLUMN product_id

mysql> alter table orders drop column product_id;

mysql> describe orders;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| order_date | date         | YES  |     | NULL    |       |
| sale       | int(11)      | YES  |     | NULL    |       |
| category   | varchar(255) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Bonus Read : How to Get Records from Today in MySQL

MySQL DROP Multiple Columns

Let’s say you want to drop multiple columns sale & category. Here’s the MySQL DROP COLUMN statement to drop multiple columns from table in MySQL

mysql> alter table orders
     drop column sale,
     drop column category;

mysql> describe orders;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| order_date | date | YES  |     | NULL    |       |
+------------+------+------+-----+---------+-------+

Bonus Read : How to Get Records from Last 7 Days in MySQL

MySQL DROP COLUMN with Foreign Key

If you try to directly DROP COLUMN with Foreign Key MySQL will throw an error. Let’s say you have the following tables

mysql> create table orders3(id int auto_increment primary key,category_id int);

mysql> create table categories(id int auto_increment primary key,name varchar(255));

mysql> ALTER TABLE orders3
     ADD CONSTRAINT fk_cat
     FOREIGN KEY (category_id)
     REFERENCES categories(id);

Bonus Read : How to Get Records Between 2 Dates in MySQL

In the above example, foreign key category_id in orders3 references primary key ID column from categories table. If you try to delete it, you will get an error.

mysql> alter table orders3 drop column category_id;
ERROR 1553 (HY000): Cannot drop index 'fk_cat': needed in a foreign key constraint

So first you need to delete foreign key constraint and only then use MySQL DROP COLUMN

mysql> alter table orders3 drop foreign key fk_cat;

mysql> alter table orders3 drop column category_id;

mysql> describe orders3;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to find largest table in MySQL database?

How to Remove Not Null Constraint in MySQL

How to calculate age from date of birth in SQL