×
☰ See All Chapters

Oracle ALTER TABLE - Changing the structure of the database tables

ALTER TABLE statement is used to change the structure of the database tables like to add, modify, drop or delete columns in a table. ALTER statement can also be used to rename the table. Using ALTER statement we can update below listed structure of a table.

  1. Add columns to table 

  2. Delete columns from table 

  3. Modifying data type of a column 

  4. Modifying length, precision, or scale of a column 

  5. Modifying column constraint 

  6. Rename table 

Adding columns to table in Oracle

One of the basic rules for adding columns to an existing table is that the column you are adding cannot be defined as NOT NULL if data currently exists in the table. If necessary to add column with NOT NULL constraint then do the following

  • Add the column and define it as NULL. (The column does not have to contain a value.)  

  • Insert a value into the new column for every row of data in the table. 

  • Alter the table to change the column’s attribute to NOT NULL. 

Syntax:

ALTER TABLE <table-name>

ADD (<column-name> <data-type> <constraint>);

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

EMAIL VARCHAR(30) UNIQUE,

CONSTRAINT CUSTOMER_UQ

UNIQUE (CUSTOMER_ID,EMAIL)

);

 

ALTER TABLE CUSTOMER

ADD (PHONE NUMBER (10));

Deleting columns from table in Oracle

Syntax:

ALTER TABLE <table-name>

DROP (<column-name>);

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT NOT NULL,

EMAIL VARCHAR(30) UNIQUE,

PHONE NUMBER (10),

CONSTRAINT CUSTOMER_UQ

UNIQUE (CUSTOMER_ID,EMAIL)

);

ALTER TABLE CUSTOMER

DROP (PHONE);

Modifying data type of a column in Oracle

Syntax:

ALTER TABLE <table-name>

MODIFY (<column-name> <new-data-type>);

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  NUMBER(10) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY(PHONE VARCHAR(20));

Modifying length, precision, or scale of a column in Oracle

Syntax:

ALTER TABLE <table-name>

MODIFY (<column-name> <data-type>(new-length));

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  VARCHAR(20) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY(PHONE VARCHAR(30));

Modifying column constraint in Oracle

Syntax:

ALTER TABLE <table-name>

MODIFY (<column-name> <data-type> <new-constraint>);

Example:

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

PHONE  NUMBER(10) NOT NULL

);

ALTER TABLE CUSTOMER

MODIFY(PHONE NUMBER NULL);

 

 

 

 

All Chapters
Author