The ALTER TABLE Statement

• Add a new column
• Modify an existing column
• Define a default value for the new column
• Drop a column

<Syntax>

ALTER TABLE     table ADD        (column datatype [DEFAULT expr ]         [, column datatype] …..);

 

ALTER TABLE    table MODIFY    (column datatype [DEFAULT expr]         [, column datatype] ……..);

 

ALTER TABLE     table DROP        (column);

- DEFAULT expr : specifies the default value for a new column

Adding a Column

Ex)

ALTER TABLE     dept80 ADD        (job_id     VARCHAR));

* The new column becomes the last column.
* If a table already contains row when a column is added, then the new column is initially null for all the rows.
* Can not add a mandatory NOT NULL column to a table that contains data in the other columns. (To an empty table, it's possible.)