SQL: ALTER TABLE Statement

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

ALTER TABLE suppliers
 RENAME TO vendors;

This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1
To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 ADD column_name column-definition;

ALTER TABLE supplier
 ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2
To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	...
	column_n	column_definition );

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1
To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2
To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	...
	column_n	column_type );

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1
To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 DROP COLUMN column_name;

ALTER TABLE supplier
 DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.


Rename column(s) in a table (NEW in Oracle 9i Release 2)