SQL – Alter command – Explanations, Syntax and examples

Once a table has been created, renaming it or changing the datatypes are common. You may also prefer to add or delete columns in a table. For this purpose, SQL provides ALTER TABLE statement to alter the characteristics of the table.

Alter Table

In SQL, ALTER TABLE statement is primarily used to add or delete columns, modify existing columns, rename a table or column, changing the data types, sizes and so on.

1) Rename table

SQL allows to overwrite the old name of the table and rename it using the syntax below:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/alter-rename.sql

In the above example, the name of the table is changed from “codingeek” to “codingeeksql”

2) Rename column

You can rename the existing column or columns in a table using the syntax as follows:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/alter-column.sql

Here, the name of the specified column is changed from “city” to “place” in codingeeksql table.

3) Add new column

One can add a new column to an existing table by mentioning the name and datatype of the column.

Consider the table “codingeeksql” as shown below:

Add a new column “Area” with type varchar(15) as (syntax along with example):

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/add-column.sql

Output:

An empty field is added to the table where the name of the column is Area and type is varchar

4) Adding multiple columns

Similarly, you can add multiple columns to an existing table by defining it using the folllowing syntax:

 
Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/multi-col.sql 

Output:

Thus, the empty columns Occupation, Address, Phone are created with datatypes varchar, varchar and integer respectively.

5) Modify existing columns

You can modify the characteristics of an existing table columns such as datatype, size, NOT NULL:

Current Structure of the codingeeksql table is:

Name                        Null?       Type
--------------------------- --------    ------
 ID                         NOT NULL     INT
 NAME                        NULL      VARCHAR(10)
 AREA                        NULL      VARCHAR(15)
 OCCUPATION                  NULL      VARCHAR(30)
 ADDRESS                     NULL      VARCHAR(50)
 PHONE                       NULL         INT

Below is the syntax and example to change the datatype to CHAR(20).

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/modify-column-datatype.sql 

Now see the changes in the structure:

Name                        Null?      Type
--------------------------- --------  ------
 ID                         NOT NULL    INT
 NAME                         NULL    VARCHAR(10)
 AREA                         NULL    CHAR(20)
 OCCUPATION                   NULL    VARCHAR(30)
 ADDRESS                      NULL    VARCHAR(50)
 PHONE                        NULL      INT

The datatype of Area is changed from varchar to char and size from 15 to 20.

6) Modify multiple existing columns

We can modify multiple columns in a similar manner. The syntax for modifying multiple columns is given as:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/multi-mod.sql 

The modifications in the datatype and size can be seen below:

Name                         Null?        Type
--------------------------- --------     ------
 ID                         NOT NULL    SMALLINT
 NAME                         NULL      CHAR(13)
 AREA                         NULL      CHAR(20)
 OCCUPATION                   NULL      VARCHAR(30)
 ADDRESS                      NULL      VARCHAR(50)
 PHONE                        NULL        INT

Modifications:

• The datatype of ID is modified from INT to SMALLINT
• The datatype and size of Name is changed from VARCHAR(10) to CHAR(13)

7) Drop column

Drop column is a synonym of deleting column in the table. Using DROP statement you can delete a particular column using the following syntax:

Unable to retrieve the Code part.
Please reload again.
Notify us if the problem still persists.
Till we work on this you can view code on URL below.
Please visit - https://github.com/HiteshGarg/codingeek/blob/master//SQL/Basic/alter-command/drop-col.sql 

From the codingeeksql table, the ID field is removed completely or “dropped” and the table is displayed with remaining fields.

Learning never exhausts the mind. Keep learning!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -