SQL Extensions   «Prev  Next»

Lesson 7 Adding or modifying a column
ObjectiveChange columns in an existing table

Adding or modifying Columns

The table you created has the right space allocation and constraints. The the inevitable happens and you need to make a change to the table's columns.
This lesson lets you practice making changes to the columns in an existing table using the ALTER TABLE command.
There are several types of changes that can be handled using the ALTER TABLE command. Some changes require a full reconstruction of the table.

Moving Dropping Column Restrictions

Oracle does not make it easy to change the position of a column or remove a column from a table. If you need to do either of these, you must drop the entire table and recreate the table with the new structure.
If you have any data in the table, you must either forfeit the data (which may be okay for test tables) or save the data in another table so that you can restore it after you restructure the table.
When you drop a table, even if you have saved the data, you must also be sure to restore all the other objects that were lost when you dropped the table, such as indexes and constraints. See the next module for more details on the effects of dropping a table.
Avoid changing the order of columns in the table. In general, revising the order of the columns makes no difference in storage space or efficiency of performance.
The table below outlines the possible changes you can make to columns and what command is needed.
Type of change Use ALTER TABLE Use DROP TABLE and then CREATE TABLE
Shorten column length Yes (if all rows contain null values in this column)  
Lengthen column length Yes  
Remove column No Yes
Add column to end of table Yes  
Add column in middle of table No Yes
Reorder the columns No Yes
Change NOT NULL to NULL Yes  
Change NULL to NOT NULL Yes (if all rows contain data in this column)  
Change datatype of column Yes (if all rows contain null values in this column)  
Rename a column No Yes
The syntax for changing a column is shown in the graphic below.
Changing Column
Changing Column

Change columns

Follow along in the simulation to practice changing the columns in a table.
1) Alter Table 1 2) Alter Table 2 3) Alter Table 3 4) Alter Table 4
  1. Syntax error,43,170,354,0,3,0,0
  2. JOB_TITLE VARCHAR2(20) NULL);
  3. Press ENTER when done.
  4. value="1,0,1,0"
  1. Good. You have now added the two new columns. Another change is needed: Allow null values in the LASTNAME column. Type the following command at the SQL> prompt and press ENTER: ALTER TABLE EMPLOYEE MODIFY (LASTNAME NULL);
  2. ALTER TABLE EMPLOYEE MODIFY (LASTNAME NULL);
  3. Check your spelling.
Adding Modifying Columns
The next lesson shows you how to drop tables and constraints.

Adding Modifying Column-Exercise

Click the Exercise link below to practice adding and modifying columns.
Adding Modifying Column-Exercise