This SQL tutorial focuses on the Oracle ALTER TABLE statement. This tutorial is the second part of two posts describing DDL (Data Definition Language) statements in Oracle.
In this post you will learn how to modify the columns within the table (such as removing, adding, and changing the data type of a column). The previous post described the concept of creating and deleting tables.
Oracle ALTER TABLE – Adding New Column
The syntax to add a column in Oracle is:
ALTER TABLE table_name ADD column_name column_datatype
For example:
ALTER TABLE products ADD discount NUMBER(8,2)
- You cannot define where the new column will be located; each newly inserted column is always located last.
- If the table already contains columns with values, the new column will be populated with NULL values for all these rows.
Oracle ALTER TABLE – Changing Column Data Type
The syntax for changing a column’s data type in Oracle is:
ALTER TABLE table_name MODIFY column_name column_datatype
For example:
ALTER TABLE products ALTER COLUMN discount NUMBER(5)
Oracle ALTER TABLE – Dropping a Column
The syntax to remove a column in Oracle is:
ALTER TABLE table_name DROP COLUMN column_name
For example:
ALTER TABLE products DROP COLUMN discount
