ALTER TABLE in Oracle

ALTER TABLE

To add, modify, drop, or delete columns in a table ALTER TABLE statement is used. Along with all these, it is also used to rename a table. The ALTER TABLE statement allows the users to Add one or more columns, Modify column definition, Drop one or more columns, Rename columns, and Rename a table.

Syntax:

ALTER TABLE table_name action;

ALTER TABLE ADD column

Syntax: To add a column in the existing table:

ALTER TABLE table_name 
ADD column_name data_type constraint;

Parameters:
table_name: It is used to specify the name of the table.
column_definition: It is used to specify the column name, data type, and constraint.

Syntax: To add multiple columns in the existing table.

ALTER TABLE table_name  
  ADD (column_1 column_definition,  
       column_2 column_definition,  
       ...  
       column_n column_definition);

Example 1: Adding a new column to an already existing table.

ALTER TABLE students  
ADD student_age varchar(20);

Explanation:
A new column “student_age” of data type VARCHAR and a maximum length of 20 will be added to the “students” table.

Example 2: Adding multiple columns to an already existing table.

ALTER TABLE students  
ADD student_age varchar(20);  
ADD student_city varchar(30);

Explanation:
Two new columns “student_age” and “student_city” of data type VARCHAR and a maximum length of 20 and 30 will be added in the “students” table.

ALTER TABLE MODIFY column

Syntax: To modify a single column of a table:

ALTER TABLE table_name 
MODIFY column_name action;

Syntax: To modify multiple columns of a table.

ALTER TABLE table_name
MODIFY (
    column_name_1 action,
    column_name_2 action,
    ...
);

Example 1: Modifying a single column of a table.

ALTER TABLE students
MODIFY student_name varchar2(50) NOT NULL;

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values.

Example 2: Modifying multiple columns of a table.

ALTER TABLE students
MODIFY (student_name varchar2(50) NOT NULL;  
student_age varchar2(40));

Explanation:
Here the “students” is an already existing table whose column student_name is modified to varchar2 (50) and the column is now restricted to not allow NULL values. Similarly, the column student_age is modified to varchar2 (40).

ALTER TABLE DROP COLUMN

Syntax:

ALTER TABLE table_name  
DROP COLUMN column_name;

Example :

ALTER TABLE students  
DROP COLUMN student_age;

Explanation:
Here, “students” is an already existing table, from which the student_age column will be dropped.

ALTER TABLE RENAME COLUMN

Syntax:

ALTER TABLE table_name  
RENAME COLUMN existing_column_name to new_column_name;

Example :

ALTER TABLE students  
RENAME COLUMN student_age to std_age;

Explanation:
Here, “students” is an already existing table. The column student_age will be renamed as std_age.

ALTER TABLE RENAME TO

Syntax:

ALTER TABLE existing_table_name  
RENAME TO new_name;

Example :

ALTER TABLE students  
RENAME TO children;

Explanation:
Here, “students” is an already existing table. The table name will be renamed “children”.

Alex Martin