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?