Using the ALTER TABLE Command
A table’s structure can be altered using the ALTER Command. The command permits the structure of the existing table to be altered through adding new columns or fields modifying and dynamically the existing fields’ datatypes. By using this command, one or more columns can be added.
Syntax:
Alter table <tablename> add (column1 datatype, column2 datatype …); Alter table <tablename> modify (column1 datatype, column2 datatype …);
Example:
Consider the previous instance where a new column known as Salary is to be added. The ALTER TABLE employee ADD (salary NUMBER);
After this command is issued, it shows,
Table altered.
Alter command can be used to change the existing datatype of a column to another datatype or to modify the column width. The next example describes this:
Example:
ALTER TABLE employee MODIFY (empname VARCHAR2 (20));
Here the number of characters which the column empname can hold is raise from 10 to 20. The maximum length of the value in the empname column is 20.
Note: In the case of decreasing the data width and the column on that the width is going to decrease must not contain any value. While, in the case of increasing, there is no such restrictions.After altering the structure through adding a new column and modifying the data width, the structure of the table would display like this:
