Alter Table SQL

Alter Table SQL

In this part of sql tutorial  , we will see the use of  alter table command of sql. After going through this part of sql tutorial , readers will be able to add columns to existing tables as well as they will be able to alter the existing columns of the tables. Sometimes after the creation of a table, need of addition of a new column exists, in that case Alter table command is helpful.

Before we discuss Alter Table command in SQL  , lets us see why to add columns in an existing table. Sometimes while deciding columns of a table, we forget to include a particular field, in that case we can add new columns to the existing tables. 

In programming , sometimes need of a new columns in existing tables arises at a later stage.

In SQL, we can add a new column in an existing table as well as we can change the specifications of the existing columns.

For video Tutorial on Alter Table command click here.

Suppose you have a table named employee in database as given below-

Eocde      Ename   Ecity

Ecode	Ename	Ecity

After creating this table, need of a new column named zip code arises. You can add this new column using Alter table command of SQL.

A table in SQL can be altered by using alter table command. By using  alter table command , we can add  new columns to an existing table. We can also change specification of the existing columns of a table using Alter Table command.

Using alter table command, one can add new columns as well as can change the specifications of the existing columns. You can change the data types of the existing columns; one can change the field width of the existing columns.

Add Column SQL

Alter Table Command

A table in SQL can be altered by using alter table command. By using  alter table command , we can add new columns to an existing table. We can also change specification of the existing columns of a table using Alter Table command.

Whenever you will try to add column in sql, you have to take care of data.

Suppose you have a table named employee as given below –

Ecode	Ename	Ecity
E001	JOE	FLORDA
E002	JIM	SYDNEY

If you add a new column named zip code in the above table, structure of the above table will be like this-

Ecode	Ename	Ecity	Zip code
E001	JOE	FLORDA
E002	JIM	SYDNEY

Here you can see Zip code column is having no values, and then what will the values of Zip code in case of these two entered records.

In that case you have to use update command to add values to these two already entered records.

 How to Add Column in sql table using Alter Table command

To add new columns in an existing table, we can use add option of alter table command.

The general syntax of alter table command with add option is given below:

Alter table <table-name> add( new column specification);

Suppose we want to add zipcode column in employee table.

SQL>Alter table employee add(zipcode number(10));

This will add a new column zipcode to employee table.

Modifying existing columns of a table

To modify specification of existing columns of a table ,we can use modify option of  alter table command.

The general syntax for modifying existing columns is given below

Alter table <table-name> modify( column new_specification);

Suppose we wants to change filed width of ecode column from 4 to 6.

SQL>Alter Table employee modify( ecode char(6));

Note: Be careful while changing column specifications, this is because when you to change the data type of an existing column, if the table is having data that time, there may be some issues of data type mismatch. Also in case of column width alteration, if you try to reduce the width of an existing column , if that column is having values, in that case truncation of data may happen.