MySQL DROP COLUMN

Summary: in this tutorial, you will learn how to drop one or more columns from the MySQL table using the MySQL DROP COLUMN statement with syntax and examples.

In some situations, you may want to drop one or more columns from the MySQL table.

MySQL DROP COLUMN statement allows us to drop one or more columns from the MySQL table. Dropping a column deletes the data which belongs to that column. So, you need to do double-check before dropping a column.

MySQL DROP COLUMN Syntax

Following is the syntax for dropping a column from the MySQL table.

ALTER TABLE table_name  DROP [COLUMN] column_name;

Let’s understand the above syntax in more detail.

First, specify the name of the table name after the ALTER TABLE keywords.

Next, specify the name of the column which you want to drop from the table. Note that the COLUMN keyword is optional here. You can omit that if you want.

You can also drop multiple columns from a table using the single ALTER TABLE statement as below.

ALTER TABLE table_name 
DROP [COLUMN] column_name_1, 
DROP [COLUMN] column_name_2, 
......;

Following are the few important points that you need to keep in mind while dropping columns from a table.

  • Removing a column from a table makes all the database objects like views, stored procedures, triggers that are referencing to the dropped column invalid. You have to manually fix the code by removing the dropped column to it valid again.
  • Any other application that depends on the dropped column must also be changed.
  • If you are dropping a column from a large table having millions of rows may take system resources and time.

MySQL DROP COLUMN Example

Let’s understand the concept of MySQL DROP COLUMN with the help of some examples.

Suppose, we have a Products table as below:

mysql drop column

1) Dropping a single column

In this example, we will drop the product_date column from the Products table.

ALTER TABLE Products
DROP COLUMN product_date;

Here is the output of the SELECT statement after the above statement completes.

mysql drop column

As you can see from the above output that the column product_date does not exist anymore.

2) Dropping multiple columns

In this example, we will drop product_description and product_stock columns from the Products table.

ALTER TABLE Products
DROP COLUMN product_description,
DROP COLUMN product_stock;

Here is the output of the SELECT statement after the above statement completes.

mysql drop column

As you can see from the above output that the columns product_description and product_stock do not exist anymore.

MySQL drop a column which is a foreign key example

If you try to drop a column that is a foreign key, MySQL issue an error.

Consider the following two tables named: courses and enrolls.

Table: courses

CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
course_name varchar(50) NOT NULL
);

Table: enrolls

CREATE TABLE enrolls (
id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT NOT NULL,
FOREIGN KEY(course_id) REFERENCES courses(id)
);

As you see that the column course_id is a foreign key in the enrolls table.

Now, try to remove the column course_id from the enrolls table as below.

ALTER TABLE enrolls
DROP COLUMN course_id;

MySQL throws an error as below.

mysql drop column