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
1 2 | ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST|AFTER existing_column]; |
Note: Syntax within square brackets is optional.
where,
table_nameis the name of the table.column_definitionis the syntax for the new column.FIRSTallows to add a new column as the first column of the MySQL table.AFTER existing_columnis used to add a new column after a particularexisting_columnin 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
1 | 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
1 2 3 4 | 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.
1 2 | ALTER TABLE pp_posts ADD COLUMN content varchar(500); |
Example 2: Adding a new column ‘date’ after column ‘id’.
1 2 | ALTER TABLE pp_posts ADD COLUMN date DATE AFTER id; |
Example 3: Adding multiple columns to table ‘pp_posts’ with single ALTER statement.
1 2 3 | 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
1 2 | ALTER TABLE table_name DROP [COLUMN] column_name; |
Note: Syntax within square brackets is optional.
where,
table_nameis the name of the table.column_nameis 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
1 2 3 4 | 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’.
1 2 | ALTER TABLE pp_posts DROP COLUMN date; |
Example 2: Deleting columns ‘category’ and ‘author’ using single ALTER statement.
1 2 3 | 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.