SQL - ALTER TABLE

The ALTER command is a DDL command to modify the structure of existing tables in the database by adding, modifying, renaming, or dropping columns and constraints. You can add columns, rename columns, delete columns, or change the data type of columns using the ALTER command.

Add Columns in the Table

Use ALTER TABLE ADD command to add new columns in the database table.

Syntax:

ALTER TABLE table_name 
ADD column_name1 data_type,
    column_name2 data_type,
    ...      

As per the above ALTER TABLE command, use the ADD keyword to add one or more new columns. Please note that data type names are different in different databases, so use the data types based on the database you are working.

In the Create Table section, we created the Employee table, as shown below. Now, let's add new columns to it using the ALTER command.

The following ALTER command adds Address, city, and Pin Code columns to the Employee table in SQL Server, MySQL, PostgreSQL, SQLite.

ALTER TABLE Employee 
ADD Address VARCHAR(100),
    City VARCHAR(25),
    PinCode integer;

The following ALTER command adds Address, city, and Pin Code columns to the Employee table in Oracle database. Notice that it supports the different data types.

ALTER TABLE Employee 
ADD(Address VARCHAR2(100),
    City VARCHAR2(25),
    PinCode NUMBER);

The above ALTER script will add new columns in the Employee table, as shown below.

Learn how to rename an existing column name in the next chapter.