SQL Alter Table

Alter Table


Alter Table Command :-By alter table command we can alter the structure/Definition of table 

Alter table Operations :- Following operation we can perform by alter table command
                 
                 1) add   
                    a) column   b) constraint    c) partition

                 2) modify 
                    a) datatype of column     b) size of datatype

                 3) enable/disable 
                    a) constraint   b) trigger

                 4) rename  
                    a) column     b) constraint     c) partition

                 5) drop 
                    a) column      b) constraint      c) trigger   d) partition

Examples:-                   
Add column/columns :-
 
SQL>alter table emp2 add description varchar2(20);
SQL>alter table emp2 add (X varchar2(20),Y varchar2(20));
Add constraint :-
 
SQL>alter table emp1 add constraint ck_sal check (sal<10000);
SQL>alter table emp2 add constraint pk_empno primary key (empno);
Modify datatypes:- (Only when data is not present in column)
SQL>alter table emp2 modify(x number,y date);
Modify the size of datatypes:- (there is no constraint to increase the size but we can de-crease the size up to max size data avaailable in the column )

SQL>alter table emp2 modify(x varchar2(30),y varchar2(30));
Disable constraint:-
 
SQL>select * from user_constraints where table_name='EMP2';
SQL>alter table emp2 disable constraint CK_JOB;
Enable constraint :-
 
SQL>alter table emp2 enable constraint CK_JOB;
Rename the column :-
 
SQL>alter table emp2 rename column x to x1;
Rename the constraint :-
 
SQL>alter table emp2 rename constraint CK_JOB to CK_JOB1;
drop a column :-
 
SQL>alter table emp2 drop column x1;
Drop a constraint:-
 
SQL>alter table emp1 drop constraint CK_SAL;
drop a partition :-
 
SQL>alter table sales drop partition p_1997;
Here "sales" is a partition Table and "p_1997" is a name of partition.
 

No comments:

Post a Comment