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:
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 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 demonstrate 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 |