SQL ALTER TABLE

The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions.

1) Add, drop, modify table columns
2) Add and drop constraints
3) Enable and Disable constraints 

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number(15,2);

SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.

Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employee to my_employee, the query would be like

RENAME employee TO my_emloyee;

--------------------------------------------------------------------------------------------

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;

For example:

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;

For example:

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 );

For example:

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;

For example:

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 );

For example:

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;

For example:

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)

Syntax #1
Starting in Oracle 9i Release 2, you can now rename a column.
To rename a column in an existing table, the ALTER TABLE syntax is:
ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;
This will rename the column called supplier_name to sname.