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 allow the users to Add one or more columns, to Modify column definition, to Drop one or more columns, to Rename columns and to 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 its 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 in 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 as “children”.