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.

Exercise 3.7

Why would this command 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 budget column to the DEPT table:

ALTER TABLE DEPT ADD (BUDGET NUMBER(7,2));

If there are no errors, the system will reply with: Table altered

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));

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 NULL values
3. Alter the table to make the column NOT NULL

A business rule is that all projects must have a manager, so the MGRPROJNO column should really be set to NOT NULL. To do this we need to update all the PROJ records, so they now have a manager.

1. First check how many projects there are:

SELECT * FROM PROJ;

Your results may vary depending on whether you have carried out the previous exercises:

    PROJNO PNAME                                BUDGET  MGRPROJNO
---------- -------------------------------- ---------- ----------
         1 ORACLE VERSION 10                     15000
         2 ORACLE DEVELOPER                      10000
         3 ORACLE VERSION 12C                     8000

2. Assuming you have the above 3 records, update the MGRPROJNO column (amend to match your own data).

Update the PROJ table so that BLAKE is the manager of project 1:

UPDATE proj SET mgrprojno = 7698 WHERE projno = 1;

Update the PROJ table so that JONES is the manager of project 2:

UPDATE PROJ SET mgrprojno = 7566 WHERE projno = 2;

and save the changes so far:

COMMIT;

Exercise 3.8

Update the PROJ table so that CLARK is the manager of project 3.

If you have any other rows in the PROJ table not covered so far, also update these so they have a EMPNO value in the MGRPROJNO column.

3. Now make the column NOT NULL:

ALTER TABLE PROJ MODIFY (MGRPROJNO NOT NULL);

Deleting a Column

A column can be deleted using the DROP clause:

ALTER TABLE table_name DROP COLUMN column_name;

For example, departments are not going to have a budget after all:

ALTER TABLE DEPT DROP COLUMN BUDGET;

Next Step

This completes the DDL section. Now return to the Workbook.