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 particularexisting_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’.
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 single ALTER
statement.
ALTER TABLE pp_posts ADD COLUMN category varchar(30), ADD COLUMN author varchar(30) NOT NULL;
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’
Example 1: Deleting single column ‘date’.
ALTER TABLE pp_posts DROP COLUMN date;
Example 2: Deleting columns ‘category’ and ‘author’ using 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.
If you have any doubts or suggestion then please comment below.