TABLE
OPERTION---ALTERING STRUCTURE
·
Introduction
·
Renaming
a table
·
Adding
column to a table
·
Removing
column from a table.
·
Increasing
width of a columns.
·
Decreasing
width a columns.
·
Changing
data types of a columns.
·
Copying
of a table.
Introduction:-
Let us turn
our focus on modifying the existing structure of table. By structure we means
column names, data types and constrains.
Renaming a table:-
Now change
the table name COLLAGE TO COLLAGES.
SQL>
select * from collage;
COLLAGE ROLLNO NAME MARKS
--------------------
---------- ---------- ----------
rkdf 1 sanjay 12
rkdf 2 salaj 34
oist 3 rahul 23
ptl 4 sunil 45
oist 5 neelesh 43
rkdf 6 ruchi 21
rkdf 7 sarika 49
rkdf 8 satish 32
oist 9 null 31
oist 10 abc
oist 11 xyz
11 rows
selected.
SQL>
rename collage to collages;
Table
renamed.
SQL>
select * from collage;
select *
from collage
*
ERROR at
line 1:
ORA-00942:
table or view does not exist
SQL> desc
collages;
Name
Null? Type
-----------------------------------------------------------------
-------- ----------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME VARCHAR2(10)
MARKS
NUMBER(5)
SQL> desc
collage;
ERROR:
ORA-04043:
object collage does not exist
Adding column to a table:-
Let us add
an extra column in class in collages table
SQL>
alter table collages add collage_name varchar2(20);
Table
altered.
OR
SQL>
alter table collages add (collage_name varchar2(20));
Table
altered.
SQL> desc
collages;
Name Null? Type
------------------------------------------------------------------------
----------------------------------
COLLAGE VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS
NUMBER(5)
COLLAGE_NAME
VARCHAR2(20)
Notes:-
The new
column will be always be added at the end of last column.
We can’t add
new columns in between existing columns or at the position.
Removing columns from a table:-
Let us
remove a column collage_name from the table.
SQL>
alter table collages drop column collage_name;
Table
altered.
SQL> desc
collages;
Name
Null? Type
------------------------------------------------------------------------
------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS NUMBER(5)
Increasing width of column:-
Let us now increasing
the with column name column to 15 character.
SQL> desc
collages;
Name
Null? Type
-----------------------------------------------------------------
-------- ------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS
NUMBER(5)
SQL>
alter table collages modify name varchar2(15);
Table
altered
SQL> desc
collages;
Name
Null? Type
------------------------------------------------------------------
-------- ----------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(15)
MARKS
NUMBER(5)
Decreasing width of a column:-
Let us
decreasing a width of name column to 10 again.
SQL>
alter table collages modify name varchar2(10);
Table
altered.
SQL> desc
collages;
Name
Null? Type
------------------------------------------------------------------
-------- ------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS
NUMBER(5)
Notes:-
Column size
can be reduced only in two cases.
·
All
the record contain NULL values for that columns.
·
The
size of data which is already present is less than or equal to the new size
specified.
Changing data types of a columns:-
We can
change data types from CHAR TO VARCHAR2 and vice-versa, that is again we change
the data types of name column from varchar2 to char.
The alter
table command can be used to change the data types.
SQL> desc
collages;
Name Null? Type
------------------------------------------------------------------
-------- -----------------------------
COLLAGE VARCHAR2(20)
ROLLNO
NUMBER
NAME
VARCHAR2(10)
MARKS
NUMBER(5)
SQL>
alter table collages modify name char(10);
Table
altered.
SQL> desc
collages;
Name
Null? Type
--------------------------------------------------------------------------------
------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
CHAR(10)
MARKS
NUMBER(5)
Notes:-
We can
change the data types from char to varchar2 and vice-versa provided the new
size is more than or equal to the size of the existing data values.
Changing number data type to char:-
SQL> desc
collages;
Name
Null? Type
--------------------------------------------------------------------------
------------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
NUMBER
NAME
CHAR(10)
MARKS
SQL>
alter table collages modify rollno char(4);
alter table
collages modify rollno char(4)
*
ERROR at
line 1:
ORA-01439:
column to be modified must be empty to change datatype
Notes:-
We can not
change the data types from NUMBER TO CHAR/VARCHAR2 and vice-versa.
First we
need to empty the data values for the column and then modify it to the new data
types.
SQL> update
collages set rollno=null;
11 rows
updated.
SQL> alter table collages modify rollno char(4);
Table
altered.
SQL> desc
collages;
Name
Null? Type
---------------------------------------------------------------------------
-----------------------------
COLLAGE
VARCHAR2(20)
ROLLNO
CHAR(4)
NAME
CHAR(10)
MARKS NUMBER(5)
Copying a table:-
A table has
two component: structure and record. Structure refer to column name, data types
and constraints. Record refer to the data which is available in the table.
To copy structure
as well as record we can use the CREATE TABLE command with SELECT AS clause.
SQL>
create table employee(id number(5) primary key,
2 name
varchar2(10) not null,
3
address varchar2(50));
Table
created.
SQL>
insert into employee values(&p_id,'&p_name','&p_addrss');
Enter value
for p_id: 1
Enter value
for p_name: satish
Enter value
for p_addrss: patna
old 1: insert into employee
values(&p_id,'&p_name','&p_addrss')
new 1: insert into employee
values(1,'satish','patna')
1 row
created.
SQL> /
Enter value
for p_id: 2
Enter value
for p_name: alam
Enter value
for p_addrss: gaya
old 1: insert into employee
values(&p_id,'&p_name','&p_addrss')
new 1: insert into employee
values(2,'alam','gaya')
1 row
created.
SQL>
commit;
Commit
complete.
SQL>
create table employee_new as select * from employee;
Table
created.
SQL> desc
employee_new;
Name
Null? Type
---------------------------------------------------------------------------
-----------------------
ID
NUMBER(5)
NAME
NOT NULL
VARCHAR2(10)
ADDRESS
VARCHAR2(50)
SQL>
select * from employee_new;
ID NAME ADDRESS
----------
---------- --------------------------------------------------
1 satish patna
2 alam gaya
Copying a table specifying different
column names:-
We can use
the aliasing concept to specifying different column names for the new table.
SQL> desc
employee;
Name
Null? Type
--------------------------------------------------------------------------
---------------------
ID
NOT NULL NUMBER(5)
NAME
NOT NULL VARCHAR2(10)
ADDRESS
VARCHAR2(50)
SQL>
create table em_diff as select id as empno,name as emp_name,address as
emp_address from employee;
Table
created.
SQL> desc
em_diff;
Name
Null? Type
-----------------------------------------------------------------
-------- --------------
EMPNO
NUMBER(5)
EMP_NAME NOT NULL VARCHAR2(10)
EMP_ADDRESS
VARCHAR2(50)
Copying table structure only not
record:-
We can just
copy structure excluding record by using WHERE clause with a condition which
always evaluates to FALSE for every record in base table.
SQL>
create table emp_structure as select * from employee where 1=2;
Table
created.
SQL> desc
emp_structure;
Name
Null? Type
---------------------------------------------------------------------------
-------------------------------
ID
NUMBER(5)
NAME
NOT NULL
VARCHAR2(10)
ADDRESS
VARCHAR2(50)
SQL>
select * from emp_structure;
no rows
selected
THANK YOU
No comments:
Post a Comment