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.
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!
