ALTER table oracle

ALTER table in a Oracle DDL command. It uses to change the structure of the table.

Using ALTER table command we can

1. Add columns
2. Drop columns
3. Modify the column
4. Rename column
5. Add constraints
6. Drop constraints

(  There are many things you could do with ALTER command in Oracle. Above are the most used and basic ones. )

Examples:-

Consider the below table structure

SQL> desc customer;
Name                  Null?                                   Type
—————————————– ——– ————-
CUST_ID                                                       NUMBER
CUST_NAME                                                VARCHAR2(10)
CUST_ADDRESS                                          VARCHAR2(50)

1. Add columns

a. Add one column to customer table

alter table  customer add cust_dob date;

or

alter table  customer add (cust_dob date); 

b. Add multiple columns to customer table

alter table  customer add
( cust_zip number, cust_phone number, cust_fax number, cust_email varchar2(20) ) ; 

2. Drop columns

a. Dropping single column

alter table customer drop column cust_email; 

b. Dropping multiple columns in a single command

alter table customer drop ( cust_phone, cust_fax) ;  — No need of ‘column’ keyword here

3. Modify column

a. Changing length of the column

alter table customer modify ( cust_name varchar2(50) ) ;

b. Changing data type of the column

alter table customer modify ( cust_zip varchar2(20) ) ;

4. Rename column

alter table customer rename column cust_zip to cust_zipcode ;

5. Add constraints

a. Adding primary key constraints

alter table customer add constraint pk_cust_id primary key (cust_id);

It is always a better to supply constraint name because it is easy to maintain later.

b. Adding foreign key constraints

SQL> desc customer;
Name              Null?                                     Type
—————————————– ——– ————
CUST_ID         NOT NULL                        NUMBER
CUST_NAME                                              VARCHAR2(50)
CUST_ADDRESS                                        VARCHAR2(50)
CUST_ZIPCODE                                         NUMBER
PROD_ID                                                      NUMBER

SQL> desc product;
Name                 Null?                                    Type
—————————————– ——– ————
PROD_ID        NOT NULL                          NUMBER
PROD_DESC                                                 VARCHAR2(50)

alter table customer add constraint fk_prod_id foreign key (prod_id) references product

( prod_id );

( Here Customer as Master Table and Product as  Child table. )

c. Adding other constraints like check, unique etc.

alter table customer add constraint chk_cust_age check (cust_age > 17 ) ;

alter table customer add constraint uq_cust_address unique (cust_address);

6. Drop constraints

alter table customer drop constraint chk_cust_age;

or in generic way

alter table customer drop constraint constraint_name;

Note : – All examples tested in Oracle 11g Release 2.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “ALTER table oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s