SQL Alter statement

The alter statement of SQL is used to Alter the existing table structure of the database.

• You can use alter statement to add new columns with column specifications.
• You can drop or delete existing columns by the Alter command.
• You can use the Alter query to modify existing column specifications. The specifications like data type, size etc.
• You can also add or drop constraints like the Primary key, not null etc by using the Alter command in SQL.

Alter example to add column

Alter query to drop column

This tutorial will show you how to add, drop or modify columns in the existing table by using alter table SQL command.

Syntax of Alter statement

Following is the Alter command syntax to add, modify and delete columns in a table. The syntax is different for MySQL, MS SQL, Oracle and MS Access databases.

Alter table to add column syntax

The general syntax of SQL Alter table to add a column:

Alter table table_name

Add col_name datatype(size);

SQL Alter to modify column in MySQL

The general syntax of Alter to modify a column in MySQL database is

Alter table table_name

Modify Column col_name datatype|not null ;

i.e. after col_name the modification you want to make. See examples below how you can use it.

Alter query to modify column in SQL Server and MS Access

The general syntax of Alter to modify a column in SQL Server and MS Access database is:

Alter table table_name

Alter Column col_name datatype|not null ;

i.e. after the col_name the modification you want to make in that column are given. See the working example below.

Alter command to delete a table column syntax

The general syntax of Alter to delete a column:

Alter table table_name

Drop col_name datatype(size);

Examples of using alter query

Let us show you examples of how to use alter table SQL with our created database and table. We will use the same database, test_db and tbl_employee table, that we created in the Create table chapter, to show a few examples of Alter statement.

Just to remind this is how our example table is created in test_db:

Now, as we have a table tbl_employee with four columns (emp_id, emp_name, emp_age and emp_salary). Also, we insert data by using the insert statement.

Demo table before Alter statement 

The alter command to add column example

In this example, we will add a new column joining_date to existing tbl_employee table. This is how the alter query to add column is:

Demo Table after adding new column

As you can see, a new column is added in the table. As the table is retrieved by using the select statement, it is showing the joining_date column with the null values. One more thing to be noted is, the joining_date column cannot be created using the not null table constraint without Default definition, as table already contains a few rows.

Alter table to drop a column example

This example shows how to drop an existing column by using the alter table drop column query. For this example, we will drop the same column, joining_date that we just created in the above example.

The Alter with drop column query will be:

Demo Table after drop column

You can see the graphic, table before and after running the Alter statement to drop a column.

Example of using Alter/Modify column in table

As we have seen in the syntax of Alter statement to modify columns that MySQL, Oracle and SQL server/ MS Access has different syntax. We will show you modify/alter the columns in both ways.

Alter statement to modify column in SQL Server/MS Access

Alter datatype of a column

In this example, we will alter the datatype of joining_date from the datetime to date. So it will be able to store the date values.

The alter query will be as follows:

Alter column to add Not Null constraint in column:

Alter query to add Primary key in table column

Suppose our table has no primary key and we want to add primary key on the emp_id column. This is how our Alter query will be:

Alter query to drop primary key constraint

Alter column in MySQL / Oracle example

Alter datatype of a column

In this example, we will alter the datatype of joining_date from datetime to date. So it will be able to store date. Th query in the MySQL or Oracle database will be:

Alter column to add Not Null constraint in a column:

Alter query to add Primary key in table column

Suppose our table has no primary key and we want to add primary key on the emp_id column while working in the MySQL or Oracle database. This is how our SQL query will be:

Alter query to drop primary key constraint

 

 

Also see: SQL Update statement