Oracle ALTER TABLE Statement

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE statement to modify the table structure.

Introduction to Oracle ALTER TABLE Statement #

To modify the structure of an existing table, you use the ALTER TABLE statement.

Here’s the basic syntax of the ALTER TABLE statement:

ALTER TABLE table_name action;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the table name which you want to modify.
Second, indicate the action that you want to perform after the table name.

The ALTER TABLE statement allows you to:

Adding a new column #

To add a new column to a table, you use the following syntax:

ALTER TABLE table_name
ADD column_name type constraint;Code language: SQL (Structured Query Language) (sql)

For example:

First, create a new table called persons:

CREATE TABLE persons(
   person_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(100) NOT NULL,
   last_name VARCHAR2(100) NOT NULL   
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, add a new column called birthdate to the persons table:

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;Code language: SQL (Structured Query Language) (sql)

Third, shows the structure of the persons table using the DESC statement:

DESC persons;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name       Null?    Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME  NOT NULL VARCHAR2(100) 
BIRTHDATE  NOT NULL DATE         Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the birthdate column appears at the end of the column list of the persons table.

Adding multiple columns #

To add multiple columns to a table at once, you place the new columns inside the parenthesis as follows:

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);Code language: SQL (Structured Query Language) (sql)

For example:

First, add two new columns to the persons table:

ALTER TABLE persons 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

Second, show the structure of the persons table:

DESC personsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE  NOT NULL DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100) Code language: SQL (Structured Query Language) (sql)

The output shows two new columns phone and email at the end of the column list of the persons table.

Modifying attributes of a column #

To modify the attributes of a column, you use the ALTER TABLE ... MODIFY statement:

ALTER TABLE table_name
MODIFY column_name type constraint;Code language: SQL (Structured Query Language) (sql)

For example:

First, change the birthdate column to a null-able column:

ALTER TABLE persons 
MODIFY birthdate DATE NULL;Code language: SQL (Structured Query Language) (sql)

Second, show the new structure of the persons table:

DESC persons;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name       Null?    Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME  NOT NULL VARCHAR2(100) 
BIRTHDATE           DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100)Code language: SQL (Structured Query Language) (sql)

The output indicates that the birthdate column is nullable.

Modifying attributes of multiple columns #

To modify attributes of multiple columns, you use the ALTER TABLE...MODIFY statement:

ALTER TABLE table_name
MODIFY ( 
   column_1 type constraint,
   column_2 type constraint,
   ...
);Code language: SQL (Structured Query Language) (sql)

For example:

First, change the phone and email column to NOT NULL columns and extend the maximum length of the email column to 255 characters:

ALTER TABLE persons 
MODIFY (
    phone VARCHAR2(20) NOT NULL,
    email VARCHAR2(255) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, show persons table structure:

DESC persons;Code language: SQL (Structured Query Language) (sql)

Output:

Name       Null?    Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME  NOT NULL VARCHAR2(100) 
BIRTHDATE           DATE          
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Dropping a column #

To remove an existing column from a table, you use the ALTER TALE ... DROP COLUMN statement:

ALTER TABLE table_name
DROP COLUMN column_name;Code language: SQL (Structured Query Language) (sql)

This statement deletes the column from the table structure and also the data stored in that column.

For example:

First, remove the birthdate column from the persons table:

ALTER TABLE persons
DROP COLUMN birthdate;Code language: SQL (Structured Query Language) (sql)

Second, show the persons table structure:

DESC persons;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name       Null?    Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME  NOT NULL VARCHAR2(100) 
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255)Code language: SQL (Structured Query Language) (sql)

The output indicate that that the birthdate column has been removed.

Dropping multiple columns #

To drop multiple columns at the same time, you use the ALTER TABLE ... DROP statement:

ALTER TABLE table_name 
DROP (column_1,column_2,...);Code language: SQL (Structured Query Language) (sql)

For example:

First, remove the phone and email columns from the persons table:

ALTER TABLE persons
DROP ( email, phone );Code language: SQL (Structured Query Language) (sql)

Second, show the table structure:

DESC persons;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name       Null?    Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME  NOT NULL VARCHAR2(100)Code language: SQL (Structured Query Language) (sql)

The email and phone columns have been removed as expected.

Renaming a column #

Since version 9i, Oracle added a clause for renaming a column as follows:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;Code language: SQL (Structured Query Language) (sql)

For example:

First, rename the first_name column to forename column:

ALTER TABLE persons 
RENAME COLUMN first_name TO forename;Code language: SQL (Structured Query Language) (sql)

Second, check the table structure:

DESC persons;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name      Null?    Type          
--------- -------- ------------- 
PERSON_ID NOT NULL NUMBER        
FORENAME  NOT NULL VARCHAR2(100) 
LAST_NAME NOT NULL VARCHAR2(100)Code language: SQL (Structured Query Language) (sql)

Renaming table #

To change the name of a table to a new one, you use the ALTER TABLE ... RENAME TO statement:

ALTER TABLE table_name
RENAME TO new_table_name;Code language: SQL (Structured Query Language) (sql)

For example:

First, rename the persons table people:

ALTER TABLE persons 
RENAME TO people;Code language: SQL (Structured Query Language) (sql)

Second, show the table structure:

DESC people;Code language: SQL (Structured Query Language) (sql)

Summary #

Use the Oracle ALTER TABLE statement to change the structure of an existing table.

Quiz #

Was this tutorial helpful?