MySQL DROP COLUMN Statement with Examples

How to Use the MySQL DROP COLUMN Statement

In this tutorial, you will learn how to drop a column from a table in MySQL. There are many reasons why you may need to drop a column from a table. For example, you may need to create a new column instead of changing an existing one, or you may not need that column anymore.

If you want to add a new column to the table, read the following tutorial:

MySQL ADD COLUMN Statement

DROP COLUMN Syntax

To drop a column in MySQL, use a statement like the following:

ALTER TABLE table_name
DROP COLUMN column_name;

• Table_name: The name of the given table that you want to delete an existing column
• Column_name: The name of an existing column that you want to delete from the given table

If you want to delete multiple columns in a single query, use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name1, column_name2, column_name3,...;

Remarks

• If a table has only one column, you cannot drop that column
• You cannot drop a column that is a foreign key. You need to drop the foreign key constraint and then drop the column.
• You cannot drop a column that is a primary key referenced by a child table

Examples

For example, suppose you have a table named customers, which you created in MySQL.It consists of 4 columns: id, customer_name, customer_code, and address.

Example 1: Drop a single column

The following SQL statement will delete the customer_name column from the "customers" table:

ALTER TABLE customers
DROP customer_name;

Example 2: Drop multiple columns

In this example, we will write a SQL statement to drop multiple columns at once.

For example, if you want to drop the customer_name and address columns from the "customers" table, a SQL statement will look like this:

ALTER TABLE customers
DROP customer_name, address;

Note that you can only delete a column if it is not referenced in any other table.

Example 3: Drop a column that is a foreign Key

For example, suppose we have two tables named "customers" and "groups," and the group_id column in the "customers" table is referenced to the id column in the "groups" table.

Table: customers

Table: groups

Now, let's write a SQL statement to delete the group_id column of the "customers" table as follows:

ALTER TABLE customers
DROP group_id;

MySQL issues an error, as shown below.

[Err] 1553 - Cannot drop index 'fk_customers_groups': needed in a foreign key constraint

Note that in this example, fk_customers_groups is the name of the foreign key constraint.

To avoid this error, you need to delete the foreign key constraint first.

ALTER TABLE customers
DROP FOREIGN KEY fk_customers_groups;

After dropping the foreign key constraint, you can drop the column.

Learn more: Add columns in MySQL

Summary

In this tutorial, you have learned how to drop a column in MySQL using the DROP COLUMN statement. If a table has only one column, you cannot drop that column. If you want to drop a column, which is a foreign key, you need to drop the foreign key constraint first before dropping the column.