Oracle ALTER TABLE

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

To modify the structure of an existing table, you use the ALTER TABLE statement. The following illustrates the syntax:

ALTER TABLE table_name action;

In this statement:

• 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:

• Add one or more columns
• Modify column definition
• Drop one or more columns
• Rename columns
• Rename table

Let’s see some examples to understand how each action works.

Oracle ALTER TABLE examples

We will use the persons table that we created in the previous tutorial for the demonstration.

Oracle ALTER TABLE ADD column examples

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

ALTER TABLE table_name
ADD column_name type constraint;

For example, the following statement adds a new column named birthdate to the persons table:

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;

If you view the persons table, you will see that the birthdate column is appended at the end of the column list:

DESC persons; 

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50) 
BIRTHDATE  NOT NULL DATE         

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

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);

See the following example:

ALTER TABLE persons 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);

In this example, the statement added two new columns named phone and email to the persons table.

DESC persons

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) 

Oracle ALTER TABLE MODIFY column examples

To modify the attributes of a column, you use the following syntax:

ALTER TABLE table_name
  MODIFY column_name type constraint;

For example, the following statement changes the birthdate column to a null-able column:

ALTER TABLE persons MODIFY birthdate DATE NULL;

Let’s verify the persons table structure again:

DESC persons

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE               VARCHAR2(20)  
EMAIL               VARCHAR2(100) 

As you can see, the birthdate became null-able.

To modify multiple columns, you use the following syntax:

 ALTER TABLE table_name
  MODIFY ( column_1 type constraint,
          column_1 type constraint,
         ...);

For example, the following statement changes the phone and email column to NOT NULLcolumns and extends the length of the email column to 255 characters:

ALTER TABLE persons MODIFY(
    phone VARCHAR2(20) NOT NULL,
    email VARCHAR2(255) NOT NULL
);

Verify the persons table structure again:

DESC persons;

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
BIRTHDATE           DATE          
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255) 

Oracle ALTER TABLE DROP COLUMN example

To remove an existing column from a table, you use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

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

The following example removes the birthdate column from the persons table:

ALTER TABLE persons
DROP
  COLUMN birthdate;

Viewing the persons table structure again, you will find that the birthdate column has been removed:

DESC persons;

Name       Null     Type          
---------- -------- ------------- 
PERSON_ID  NOT NULL NUMBER        
FIRST_NAME NOT NULL VARCHAR2(50)  
LAST_NAME  NOT NULL VARCHAR2(50)  
PHONE      NOT NULL VARCHAR2(20)  
EMAIL      NOT NULL VARCHAR2(255) 

To drop multiple columns at the same time, you use the syntax below:

ALTER TABLE table_name 
DROP (column_1,column_2,...);

For example, the following statement removes the phone and email columns from the persons table:

ALTER TABLE persons
DROP
  ( email, phone );

Let’s check the persons table again:

DESC persons;

Name       Null     Type         
---------- -------- ------------ 
PERSON_ID  NOT NULL NUMBER       
FIRST_NAME NOT NULL VARCHAR2(50) 
LAST_NAME  NOT NULL VARCHAR2(50)  

The email and phone columns have been removed as expected.

Oracle ALTER TABLE RENAME column example

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

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;

For example, the following statement renames the first_name column to forename column:

ALTER TABLE persons 
RENAME COLUMN first_name TO forename;

The following statement checks the result:

DESC persons;

Name      Null     Type         
--------- -------- ------------ 
PERSON_ID NOT NULL NUMBER       
FORENAME  NOT NULL VARCHAR2(50) 
LAST_NAME NOT NULL VARCHAR2(50) 

Oracle ALTER TABLE RENAME table example

To give a table a new name, you use the following syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

For example, the statement below renames the persons table to people table:

ALTER TABLE persons RENAME TO people;

In this tutorial, you have learned how to use the Oracle ALTER TABLE statement to change the structure of an existing table.