| Lesson 7 | Adding or modifying a column |
| Objective | Change 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
There are several types of changes that can be handled using the
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.
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.
| 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 |
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);
- ALTER TABLE EMPLOYEE MODIFY (LASTNAME NULL);
- Check your spelling.
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
Adding Modifying Column-Exercise
