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.
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.
In Oracle 23ai, as in earlier versions of Oracle, the `ALTER TABLE` statement is used to change the structure of an existing table.
Two of the most commonly used clauses within this command are `ADD` and `MODIFY`. Here's a detailed comparison of the two:
๐ `MODIFY` vs โ `ADD` in Oracle 23ai
Feature |
ADD |
MODIFY |
Purpose |
Adds new columns to a table |
Changes the definition of existing columns |
Common Use Cases |
Adding new fields such as created_by , updated_at |
Changing data types, size, nullability, or default values |
Keyword Syntax |
ALTER TABLE table_name ADD (column_name datatype); |
ALTER TABLE table_name MODIFY (column_name datatype); |
Constraints |
Can define new constraints (e.g., NOT NULL, DEFAULT) |
Can alter constraints (e.g., make a column NOT NULL) |
Oracle 23ai Compatibility |
Fully supported, and can be used with newer features like Invisible Columns and Blockchain Tables |
Supports newer constraints like ROW DROP or Auto-Increment when modifying columns |
๐ง `ADD` โ Adds New Columns
ALTER TABLE employees
ADD (bonus NUMBER(7,2));
- Appends
bonus
column to the employees
table.
- All existing rows will have
NULL
in this new column unless a default is specified.
๐ `MODIFY` โ Modifies Existing Columns
ALTER TABLE employees
MODIFY (last_name VARCHAR2(100));
- Changes the
last_name
column's size from, say, 50 to 100 characters.
- Useful for adapting schema without dropping and recreating the column.
โ
Oracle 23ai Enhancements to Be Aware Of:
Invisible Columns (still usable with `ADD` and `MODIFY`):
ALTER TABLE employees ADD (legacy_code VARCHAR2(10) INVISIBLE);
Auto-Increment via Identity Columns (only `MODIFY` when changing properties):
ALTER TABLE invoices MODIFY (invoice_id GENERATED ALWAYS AS IDENTITY);
โ ๏ธ Caution
You cannot `MODIFY` a column to change its name and should use `RENAME` instead:
ALTER TABLE employees RENAME COLUMN old_name TO new_name;
Be careful with `MODIFY` on columns that are referenced by constraints, indexes, or foreign keys.