Oracle:DDL Alter

Making changes to existing tables

Once a table has been created you may wish to alter it to add new columns, amend the size of existing ones, or drop them completely. This is done using the ALTER TABLE command.

Altering Columns

Existing columns in a table can be altered by using the MODIFY clause:

ALTER TABLE TABLENAME MODIFY (COLUMN DEFINITION);

For example, to alter the BUDGET column of PROJ to accept numbers up to nine digits:

ALTER TABLE PROJ
  MODIFY (BUDGET NUMBER(9,2));

The system will respond with: - Table altered

Note, there are restrictions on altering an existing column. The width of a column can be decreased and the data type changed, but only if the column contains NULL values. If there is existing data in the column, the only change allowed is to increase the width.

A column can be changed from being NOT NULL to allowing NULL values, by adding the NULL clause at the end of the column specification. For example :

ALTER TABLE PROJ
   MODIFY (BUDGET NULL);

You can not do the reverse if some of the rows in the column affected contain null values. For example, can you see why this would fail:

ALTER TABLE EMP
  MODIFY (COMM NOT NULL);

Adding a Column

Extra columns can be added to a table using the ADD clause:

ALTER TABLE TABLENAME ADD (COLUMN DEFINITION)

For example, add a column MGRPROJNO to the table PROJ (the manager of the project):

ALTER TABLE PROJ
    ADD (MGRPROJNO NUMBER constraint fk_empmgr REFERENCES EMP(empno));

The system replies with: - Table altered.

All fields in the new column will be initially set to NULL. To make a column contain only non-NULL values: 1. Add the new column 2. Add values to every field, so there are no non-NULL values 3. Alter the table to make the column NOT-NULL