SQL - ALTER TABLE

The SQL ALTER TABLE statement is used to add, delete or modify columns in an existing table. It is also used to add or drop SQL constraints on an existing table.

Syntax

The syntax for using ALTER TABLE statement to add or drop columns are given below:

/* ADD Column */
ALTER TABLE table_name
ADD column_name datatype;

/* DROP Column */
ALTER TABLE table_name
DROP COLUMN column_name;

The syntax for using ALTER TABLE statement to modify columns is given below:

/* SQL Server / MS Access */
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

/* MySQL / Oracle (prior 10G version) */
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

/* Oracle 10G and later */
ALTER TABLE table_name
MODIFY column_name datatype;

Example:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age
1	John	London	25
2	Marry	New York	24
3	Jo	Paris	27
4	Kim	Amsterdam	30

Add a column: To add a column named Salary in the Employee table, the SQL code is given below:
```
ALTER TABLE Employee
ADD Salary DECIMAL(18,2);
```
This will produce the result as shown below:

EmpID Name City Age Salary

1 John London 25

2 Marry New York 24

3 Jo Paris 27

4 Kim Amsterdam 30
Drop a column: To drop a column named Salary in the original Employee table, the SQL code is mentioned below:
```
ALTER TABLE Employee
DROP COLUMN Age;
```
This will produce the below mentioned result:

EmpID Name City

1 John London

2 Marry New York

3 Jo Paris

4 Kim Amsterdam
Modify a column: In the original Employee table, the datatype of City column is VARCHAR(100). To change the datatype of the column to VARCHAR(255), the SQL code is given below:
```
/* SQL Server / MS Access */
ALTER TABLE Employee
ALTER COLUMN City VARCHAR(255);

/* MySQL / Oracle (prior 10G version) */
ALTER TABLE Employee
MODIFY COLUMN City VARCHAR(255);

/* Oracle 10G and later */
ALTER TABLE Employee
MODIFY City VARCHAR(255);
```
This will change the datatype of City column from VARCHAR(100) to VARCHAR(255). For a complete reference of all the data types available in SQL database, see Data Types reference.