ALTER TABLE command Syntax[Oracle]

Basic syntax for the ALTER TABLE command in Oracle

Alter Table Syntax — ALTER_TABLE tablename ADD/MODIFY/DROP action_specific_syntax

ALTER TABLE: The required ALTER TABLE keywords begin the DDL statement which allows you to modify an existing table.
tablename: The tablename must identify an existing table. You may have to qualify the tablename with its schema name, as in schema.
ADD/MODIFY/DROP: The ADD, MODIFY, or DROP keywords specify the basic type of action you want to perform on the table.
action_specific_syntax: The action specific_syntax can include a range of options, based on how you wish to modify the existing table structure.

Character Width and NUMBER Precision

Specifying the maximum length for character (CHAR and VARCHAR2) columns and the precision for NUMBER columns has consequences that must be considered during the design of the table. Improper decisions can be corrected later, using the alter table command, but the process can be difficult.

Altering Tables

Table definitions can be altered in several ways:

by adding a column to an existing table,
by changing the definition of a column,
or by dropping a column.

DBAs commonly alter tables in other ways to manage space and memory usage. Adding a column is straightforward, and this process is similar to creating a table. Suppose you decide to add two new columns to the TROUBLE table: Condition, which you believe should be NOT NULL, and Wind, for the wind speed. The first attempt starts by creating the table and populating it with a few records:

drop table TROUBLE;

create table TROUBLE (
City VARCHAR2(13),
SampleDate DATE,
Noon NUMBER(4,1),
Midnight NUMBER(4,1),
Precipitation NUMBER);

insert into TROUBLE values
('PLEASANT LAKE','21-MAR-03', 39.99, -1.31, 3.6);
insert into TROUBLE values
('PLEASANT LAKE','22-JUN-03', 101.44, 86.2, 1.63);
insert into TROUBLE values
('PLEASANT LAKE','23-SEP-03', 92.85, 79.6, 1.00003);
insert into TROUBLE values
('PLEASANT LAKE','22-DEC-03', -17.445, -10.4, 2.4);

The first attempt at adding the new columns looks like this:

alter table TROUBLE add (
Condition VARCHAR2(9) NOT NULL,
Wind NUMBER(3)
);

alter table TROUBLE add (
*

ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column
You get an error message because you cannot add a column defined as NOT NULL, when you try to add it, the column will not have anything in it. Each row in the table would have a new empty column defined as NOT NULL.
Prior to Oracle 11g, the alter table command's add clause works with a NOT NULL column if the table is empty, but usually it is impractical to empty a table of all its rows just to add a NOT NULL column.
The alternative is to first alter the table by adding the column without the NOT NULL restriction:

alter table TROUBLE add (
Condition VARCHAR2(9),
Wind NUMBER(3)
);

Table altered.
Then, you fill the column with data for every row (either with legitimate data or a placeholder until legitimate data can be obtained):

update TROUBLE 
set Condition = 'SUNNY';