How to Add and Delete Columns in MySQL?

Summary: In this tutorial, we will learn how to add columns to the table in MySQL and how to delete existing columns from the table in the MySQL database.

Prerequisite: MySQL Table

Add Columns to the Table

The general MySQL syntax to add a single column into a table is

ALTER TABLE table_name
ADD [COLUMN] column_definition [FIRST|AFTER existing_column];

Note: Syntax within square brackets is optional.

where,

• table_name is the name of the table.
• column_definition is the syntax for the new column.
• FIRST allows adding a new column as the first column of the MySQL table.
• AFTER existing_column is used to add a new column after a particular existing_column in the MySQL table.

If neither of FIRST and AFTER is specified then the new column will be inserted at the last of the MySQL table.

The general syntax for the column_definition used in the add column ALTER statement is

column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [column_constraint];

If you need to add multiple new columns to the table then you should use the following syntax

ALTER TABLE table
ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column],
ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column],
...;

Let’s see some examples for a better understanding.

Examples

We will add new columns to the following existing table named ‘pp_posts’.

Example 1: Adding a new column ‘content’ to ‘pp_posts’ table.

ALTER TABLE pp_posts 
ADD COLUMN content varchar(500);

Example 2: Adding a new column ‘date’ after column ‘id’.

ALTER TABLE pp_posts 
ADD COLUMN date DATE AFTER id;

Example 3: Adding multiple columns to table ‘pp_posts’ with a single ALTER statement.

ALTER TABLE pp_posts 
ADD COLUMN category varchar(30), 
ADD COLUMN author varchar(30) NOT NULL;

Delete Existing Columns from the Table

The general syntax to delete or drop existing columns from the table in MySQL is

ALTER TABLE table_name
DROP [COLUMN] column_name;

Note: Syntax within square brackets is optional.

where,

• table_name is the name of the table.
• column_name is the name of the column.

If you need to delete multiple columns at the same time using a single ALTER statement then use the following syntax

ALTER TABLE table_name
DROP [COLUMN] column1_name,
DROP [COLUMN] column2_name,
...;

Examples

Here are some examples in which we are deleting existing columns from the following ‘pp_posts’ table.

Example 1: Deleting single column ‘date’.

ALTER TABLE pp_posts 
DROP COLUMN date;

Example 2: Deleting columns ‘category’ and ‘author’ using a single ALTER statement.

ALTER TABLE pp_posts 
DROP COLUMN category, 
DROP COLUMN author;

Note: When you try deleting a column having a Foreign Key constraint, then MySQL throws an error. In that case, drop the foreign key constraint then try deleting the column.

I hope now you will be able to add and delete columns in MySQL using ALTER statement.