Alter table query can be used to modify the schema of existing table.
Following is the query syntax
ALTER TABLE table_name action_;
- table_name – Name of the table to modify schema of.
- action_ : modification to do in table.
Following are possible modifications actions.
- To add new column.
- To Modify schema of table column
- To rename the column
- To delete existing column.
- To rename the table.
Suppose we have following table “employee” with schema definition as in below already in database.
lets execute following query to add column named “contactno”.
alter table employee add contactno number not null;
As in above screenshot it has added new column “CONTACTNO”.
- To modify Schema of table column.
Suppose we want to modify schema of “contactno” column from number to varchar2(13);
Syntax
ALTER TABLE table_name MODIFY column_name column_datatype column_constraint.
Lets execute following query.
Alter table employee modify contactno varchar2(13);
As in above screenshot, contactno column type is changed from number to varchar2(13). Please note, We have not specified “not null” in above query since it is already not null & we dont intend to change column constraint.
Following is query syntax to do rename of column.
ALTER TABLE table_name RENAME COLUMN column_oldname TO column_newname;
suppose we have following table “employee” in database.
Suppose we want to change “contactno” column name to “personalcontactno” column.
we execute the following query.
alter table employee rename column contactno to personalcontactno;
as in screenshot, “contactno” column is renamed to “personalcontactno” after executing alter table query.
- To delete existing column.
Syntax for delete column query to drop multiple columns.
ALTER TABLE table_name DROP (column_name1,column_name2…)
Syntax for delete column query to drop single column.
ALTER TABLE table_name DROP column column_name;
Suppose we have following table definition of “employee” in the database.
Suppose we want to delete the column “personalcontactno”, lets execute following query.
alter table employee drop column personalcontactno;
As seen in above screenshot, after executying the query, column “personalcontactno” has been removed from table definition.
- To Rename the table.
Syntax to rename the table name.
ALTER TABLE table_oldname RENAME TO table_newname;
Suppose we have following table “employee” in the datatabase, that we want to rename to “people”.
Lets execute following query.
alter table employee rename to people;
As seen in above screenshot, after query execution “employee” table has been renamed to “people”. If we try to do desc query with old table name “employee” it will fail.
SoftFeeds 