MySQL Alter table

A few points about alter table in MySQL:

• To modify table structure in MySQL database, we use the Alter Table statement.
• The structure modifications mean adding columns after the table was created by Create table query.
• Similarly, you can delete columns from existing tables by using Alter table MySQL.
• The column constraints can also be added or removed by using Alter table statement e.g. adding a primary key, Unique keys etc.
• You can also modify column specifications like datatype, its size etc by using the Alter table statement.

In this tutorial, we will show you how to use Alter command in MySQL with different examples. Let us first look at its syntax.

The Alter table syntax

This is how you can use Alter table statement:

Adding a column

This is how you can add column by Alter table command:

Alter table table_name

Add col_name datatype(size);

Modify a column syntax

To modify a column in existing table:

Alter table table_name

Modify Column col_name datatype|not null ;

i.e. after col_name the modification you want to make.

Delete table column syntax

To delete an existing column from a table:

Alter table table_name

Drop col_name datatype(size);

Alter table examples

Following are a few examples of using alter table command where we are going to use our created table, tbl_employee. The table is created by using four columns initially as shown in the query below:

Adding a column example

The following example will add a column in tbl_employee table. A new column joining_date will be added by using alter table query as follows:

A new column, joining_date is added to existing table. If you look at data retrieved after adding the column, it will show Null values for the joining_date column. You cannot create joining_date with Not Null constraint in that situation because the table already contains records.

A drop column example

Following example shows how to drop a column by alter table MySQL query. We will remove a column that we just added in the above example

The query and table output after executing this command can be seen by clicking the link below:

The Alter table query to drop a column:

Modify a column example

Alter datatype of a column

As mentioned earlier, you can use alter table command to modify table columns specification like its datatype, size etc. Following example shows how to do it where we will change the datatype of the joining_date column to date datatype.

The alter table to modify column:

Adding a Not Null constraint example

Adding a primary key example

In the following example, we will add a primary key in our example table, tbl_employee:

Dropping a primary key example

Also see: MySQL drop database