CHANGING STRUCTURE AND DATA

Altering the structure of a table :

It is possible to modify the structure of the table even after the table is created. ALTER TABLE command is used to alter the structure of the table.

Adding a new column
Adding a new table level constraint
Increasing and decreasing width of a column
Changing data type of the column
Dropping a column
Dropping a constraint
Disabling a constraint
Enabling a disabled constraint

The following is the syntax of ALTER TABLE command.

ALTER TABLE Syntax

ALTER TABLE tablename
[ ADD ( column specification ) ]
[ MODIFY ( column specification) ]
[ DROP constraint-name [CASCADE]
| column [CASCADE CONSTRAINTS]
[ DROP UNUSED COLUMN [column] ]
[SET UNUSED column]
[ ENABLE | DISABLE constraint-name ]

Let us now examine various examples using ALTER TABLE command. It is important to note that some possibilities are applicable only when certain conditions are met. These if and buts are to be remembered while modifying the structure of the table.

Adding a new column or constraint :

It is always possible to add a new column to an existing table. However, if column is to be added to a table that already contains rows, then certain options are not available.

To add a new column CAT (category) to COURSES table, enter the following:
ALTER TABLE courses
ADD (cat varchar2(5));
It is not possible to given any constraint that is not satisfied by existing data. For instance, it is not possible to add CAT as a NOT NULL column as Oracle initializes CAT column in all rows with NULL value. See the following snapshot of the screen.
SQL> alter table courses
2 add ( cat varchar2(5) not null);
alter table courses
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

However, it is possible to have NOT NULL constraint in the following cases:

• If DEFAULT option is used to specify the default value for the column
• When table is empty.

The following example will work as DEFAULT option is used to specify the default value.

alter table courses 

add ( cat varchar2(5) default 'prog' not null);

You can add a table constraint. Once table is created, it is not possible to add constraints other than NOT NULL to columns of the table. However, it is possible to add any  constraint at the table level as follows:

alter table courses
add ( constraint courses_cat_chk check (length(cat) >= 2))
We will see more about functions such as length later in this book. The above constraint
specifies that the column CAT should have at least two characters. It is added as a table constraint as it not possible to add CHECK constraint at the column level to column that already exists.

Modifying attributes of existing columns :

It is also possible to modify the certain attributes of an existing column. The following are possible modifications.

• Increasing the length of the column
• Decrease the length of the column only when column is empty.
• Change the datatype of the column only when column is empty.
• Adding NOT NULL constraint. No other constraint can be added to column. However, it is
• possible to add constraints at table level. See the previous section.

To increase the size of the column CAT, enter the following:

alter table courses
modify (cat varchar2(10));

Note: You can decrease the width and even change the datatype of the column if the column is empty. That means if no row has any value for the column being altered.