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
Change columns
Follow along in the simulation to practice changing the columns in a table.
Syntax error,43,170,354,0,3,0,0
JOB_TITLE VARCHAR2(20) NULL);
Press ENTER when done.
value="1,0,1,0"
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);