MySQL DROP COLUMN Statement with Examples


How to Use the MySQL DROP COLUMN Statement

In this tutorial, we'll learn how to drop a column from a table in MySQL. There are many reasons why we need to drop a column from a table. In some case, for example, we need to create a new one instead of altering the existing column, or we don't need it any more.

DROP COLUMN Syntax

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

ALTER TABLE table_name
DROP COLUMN column_name;

  • table_name: The name of a give table you want to alter and drop an existing column.
  • column_name: The name of an existing column you want to remove from a given table.

If you want to drop multiple columns in a single statement, use the following DROP COLUMN statement:

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

Remarks

  • If a table contains only one column, the column cannot be dropped.
  • The column that is a foreign key cannot be dropped. You must drop the foreign key constraint before dropping the column.
  • The primary key column that is referenced from child tables cannot be dropped.

Examples

For example, let's say we have the customers table we've created in MySQL. This table contains 4 columns, id, customer_name, customer_code, and address.

idcustomer_namecustomer_codeaddress

Example 1: Drop a Single Column

The following statement removes the customer_name column from the customers table:

ALTER TABLE customers
DROP customer_name;

Example 2: Drop Multiple Columns

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

For example, let's say we need to drop the customer_name and address columns from the customers table, then we can use the following statement:

ALTER TABLE customers
DROP customer_name, address;

Note that we can drop any column only if that column is not referenced to another tables.

Example 3: Drop a Column that is a Foreign Key

For example, let's say we have two tables, customers and groups. The group_id column in the customers table is referenced to the id column in the groups table.

Table: customers

idcustomer_namecustomer_codeaddressgroup_id
1Customer A#CID_10011
2Customer B#CID_10021
3Customer C#CID_10032
4Customer D#CID_1004

Table: groups

idgroup_name
1VIP Customer
2Regular Customer
3Wholesales Customer

Now let's write a statement to drop the group_id column in the customers table as follows:

ALTER TABLE customers
DROP group_id;

MySQL issues an error as show below.

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

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

To avoid this error, we need to drop the foreign key constrain first.

ALTER TABLE customers
DROP FOREIGN KEY fk_customers_groups;

After you drop a foreign key constraint, now you can drop the column.

In this tutorial, we've learn how to drop a column in MySQL using the DROP COLUMN statement. If a table contains only one column, the column cannot be dropped. If you want to drop a column that is a foreign key, you need to drop a foreign key constraint first before dropping the column.

Leave a Reply

Your email address will not be published. Required fields are marked *