How to Add and Delete Columns in MySQL?

In this post, we will discuss how to add columns into the table in MySQL and how to delete existing columns from the table in MySQL database.

Prerequisite: MySQL Table

Add Columuns 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 to add 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 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 better understanding.

Examples

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

add column in MySQL

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

ALTER TABLE pp_posts 
ADD COLUMN content varchar(500);

Add single column in MySQL

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

ALTER TABLE pp_posts 
ADD COLUMN date DATE AFTER id;

Add after a column in MySQL

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

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

Add multiple columns in MySQL

Delete Existing Columuns 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 single ALTER statement then use the following syntax 

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

Examples

Here are some example where we are deleting existing columns from the table ‘pp_posts’

Already existing table in MySQL

Example 1: Deleting single column ‘date’.

ALTER TABLE pp_posts 
DROP COLUMN date;

Delete single column in MySQL

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

ALTER TABLE pp_posts 
DROP COLUMN category, 
DROP COLUMN author;

Delete multiple columns in MySQL

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.

If you have any doubts or suggestion then please comment below.

Leave a Reply

3 + 12 =