Sunday, 4 August 2013

Adding Column in table and Renaming column

Bismillahir Rahmanir Raheem
Today we are going to learn how to add columns in a table after it have been created.

So lets get started.

Adding column in a table--
We use the alter table statement to add one or more columns to a table.

create table exm(id number,name varchar2(20));

a)alter table exm add (age number);
    or in case of adding only a single column ,you can avoid using parentheses
  alter table exm add age number;

Note-while adding more than one column you must have to use parentheses
alter table exm add(class number,phone varchar2(20));

General form of alter table statement

ALTER TABLE table_name clause;

For each column clause specifies one or more of the following three elements

1.Datatype and datatype specification-required
2.DEFAULT and default value-optional
3.Constraint definition-optional

So in the above table exm we have added columns using just first element which is must
But we could have specified default value for that column and any constraint associated at the time of adding column.

So now lets do that.
alter table exm add enroll varchar2(20) default 'Fresher';
alter table exm add backlogs varchar2(20) default 'No' not null;

1.while creating not null constraint(without default statement) with column definition ,it will only be possible
  if their is no data (no row) in the table

2.When their is already entries in the table then you must use default clause with some default value 

Renaming a column--
alter table exm rename column id to identity;

No comments:

Post a Comment