SQL ALTER TABLE

SQL ALTER TABLE command can add, delete, or modify columns of an existing table.

This article discusses the SQL ALTER TABLE statement with examples and syntax.

ALTER TABLE STATEMENT

The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing table. The ALTER TABLE statement is also used to add and remove various constraints on existing tables.

It allows for structural changes like adding new columns, modifying existing ones, deleting columns, and renaming columns within a table.

Syntax

To alter/modify the table use the ALTER TABLE syntax:

ALTER TABLE table_name
clause [column_name] [datatype];

Here, the clause is the operational clause of the ALTER TABLE statement. Some key clauses of the ALTER TABLE statement are:

ADD – To add a new column to the table:

ALTER TABLE table_name
ADD column_name datatype;

MODIFY/ALTER – To change the data type of an existing column:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

DROP – To delete an existing column from the table:

ALTER TABLE table_name
DROP COLUMN column_name;

RENAME COLUMN – To rename an existing column:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

RENAME TO – To rename the table itself:

ALTER TABLE table_name
RENAME TO new_table_name;

SQL ALTER TABLE Examples

Below are the examples of ALTER TABLE statement. These examples demonstrates different use cases and shows how to use ALTER TABLE statement in SQL.

SQL ALTER TABLE ADD Column Example

The following SQL query adds an “Email” column to the “Students” table:

ALTER TABLE Students
ADD Email varchar(255);

SQL ALTER TABLE DROP Column Example

The following query deletes the “Email” column from “Students” table:

ALTER TABLE Students
DROP COLUMN Email;

SQL ALTER TABLE MODIFY Column Example

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

SQL ALTER TABLE Queries

Suppose there is a student database:

ROLL_NO	NAME
1	Ram
2	Abhi
3	Rahul
4	Tanu

To ADD 2 columns AGE and COURSE to table Student.

Query:

 ALTER TABLE Student ADD 
 (AGE number(3),COURSE varchar(40));

Output: 

ROLL_NO	NAME	AGE	COURSE
1	Ram
2	Abhi
3	Rahul
4	Tanu

MODIFY column COURSE in table Student.

Query:

 ALTER TABLE Student 
 MODIFY COURSE varchar(20);

After running the above query the maximum size of the Course Column is reduced to 20 from 40.

DROP column COURSE in table Student.

Query:

ALTER TABLE Student 
DROP COLUMN COURSE;

Output:

ROLL_NO	NAME	AGE
1	Ram
2	Abhi
3	Rahul
4	Tanu