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”.