ALTER TABLE - (Oracle Tables)

In Oracle, the ALTER TABLE statement is used to modify an existing table, such as adding or deleting columns, modifying existing columns, or renaming the table itself.

Syntax

The basic syntax for using the ALTER TABLE statement in Oracle is as follows:

ALTER TABLE table_name
ADD column_name datatype [DEFAULT default_value] [constraint];

ALTER TABLE table_name
MODIFY (column_name datatype [DEFAULT default_value] [constraint]);

ALTER TABLE table_name
DROP COLUMN column_name;

Here, table_name is the name of the table that you want to modify, column_name is the name of the column that you want to add, modify or delete, datatype is the data type of the column, default_value is the default value for the column and constraint specifies any constraint to be applied to the column.

The ADD clause is used to add a new column to the table. The MODIFY clause is used to modify an existing column. The DROP COLUMN clause is used to delete a column from the table.

Example

Let's look at some examples of using the ALTER TABLE statement in Oracle:

-- Add a new column to the table
ALTER TABLE employee
ADD email VARCHAR(50) DEFAULT 'NA' NOT NULL;

-- Modify an existing column
ALTER TABLE employee
MODIFY salary NUMBER(7,2);

-- Delete a column from the table
ALTER TABLE employee
DROP COLUMN middle_name;

Output

The ALTER TABLE statement is executed successfully when there are no errors. The output of the statement is not displayed.

Explanation

In the above example, we are using the ALTER TABLE statement to add a new column called email to the employee table, with a data type of VARCHAR(50), default value of 'NA' and the NOT NULL constraint. We are also modifying the salary column to have a maximum of 7 digits and 2 decimal points. Finally, we are deleting the middle_name column from the employee table.

Use

The ALTER TABLE statement is used to modify an existing table in Oracle, allowing you to change the structure of the table without having to recreate it from scratch. This can be useful when you need to add or delete columns, or modify existing columns to suit changing business requirements.

Important Points

• The ALTER TABLE statement is used to modify an existing table in Oracle.
• The ADD clause is used to add a new column to the table.
• The MODIFY clause is used to modify an existing column.
• The DROP COLUMN clause is used to delete a column from the table.

Summary

In summary, the ALTER TABLE statement in Oracle is a powerful tool that allows you to modify an existing table by adding, modifying or deleting columns. It is useful when you need to change the structure of a table without having to recreate it from scratch. However, caution should be exercised when using this statement, as it can cause data loss or corruption if not used properly.