oracle,alter table

文章来源
http://blog.csdn.net/BOBO12082119/article/details/6323727

//建测试表   
create table dept(  
       deptno number(3) primary key,  
       dname varchar2(10),  
       loc varchar2(13)   
       );  
create table employee_info(  
       empno number(3),  
       deptno number(3),  
       ename varchar2(10),  
       sex char(1),  
       phone number(11),  
       address varchar2(50),  
       introduce varchar2(100)  
       );  
--  
//0.重命名   
  //0.1 表:rename dept to dt;   
           rename dt to dept;  
  //0.2 列:alter table dept rename column loc to location;   
           alter table dept rename column location to loc;  
//1.添加约束   
  //1.1 primary key   
      alter table employee_info add constraint pk_emp_info primary key(empno);  
  //1.2 foreign key   
      alter table employee_info add constraint fk_emp_info foreign key(deptno)  
      references dept(deptno);  
  //1.3 check   
      alter table employee_info add constraint ck_emp_info check  
      (sex in ('F','M'));  
  //1.4 not null   
      alter table employee_info modify phone constraint not_null_emp_info not null;  
  //1.5 unique   
      alter table employee_info add constraint uq_emp_info unique(phone);  
  //1.6 default   
      alter table employee_info modify sex char(2) default 'M';  
//2.添加列   
   alter table employee_info add id varchar2(18);  
   alter table employee_info add hiredate date default sysdate not null;  
//3.删除列   
   alter table employee_info drop column introduce;  
//3.修改列   
  //3.1 修改列的长度   
      alter table dept modify loc varchar2(50);  
  //3.2 修改列的精度   
      alter table employee_info modify empno number(2);  
  //3.3 修改列的数据类型   
      alter table employee_info modify sex char(2);  
  //3.4 修改默认值   
      alter table employee_info modify hiredate default sysdate+1;  
//4.禁用约束   
  alter table employee_info disable constraint uq_emp_info;  
//5.启用约束   
  alter table employee_info enable constraint uq_emp_info;  
//6.延迟约束   
  alter table employee_info drop constraint fk_emp_info;  
  alter table employee_info add constraint fk_emp_info foreign key(deptno)  
        references dept(deptno)  
  deferrable initially deferred;  
//7.向表中添加注释   
  comment on table employee_info is 'information of employees';  
//8.向列添加注释   
  comment on column employee_info.ename is 'the name of employees';  
  comment on column dept.dname is 'the name of department';  
//9.清除表中所有数据   
  truncate table employee_info;  
//10.删除表   
  drop table employee_info;  
--  
//下面来看看刚刚才我们对表dept和表employee_info所做的更改   
//user_constraints视图里面包含了刚刚才我们创建的所有约束，以及其他信息，   
//你可以用desc user_constraints命令查看其详细说明   
select constraint_name,constraint_type,status,deferrable,deferred  
from user_constraints  
where table_name='EMPLOYEE_INFO';  
--  
CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     DEFERRED  
------------------------------ --------------- -------- -------------- ---------  
PK_EMP_INFO                    P               ENABLED  NOT DEFERRABLE IMMEDIATE  
FK_EMP_INFO                    R               ENABLED  DEFERRABLE     DEFERRED  
NOT_NULL_EMP_INFO              C               ENABLED  NOT DEFERRABLE IMMEDIATE  
SYS_C005373                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
UQ_EMP_INFO                    U               ENABLED  NOT DEFERRABLE IMMEDIATE  
CK_EMP_INFO                    C               ENABLED  NOT DEFERRABLE IMMEDIATE  
//我们可以通过user_cons_columns视图查看有关列的约束信息；   
select owner,constraint_name,table_name,column_name  
from user_cons_columns  
where table_name='EMPLOYEE_INFO';  
--  
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME  
------------------------------ ------------------------------ ------------------------------ ---------------  
YEEXUN                         PK_EMP_INFO                    EMPLOYEE_INFO                  EMPNO  
YEEXUN                         CK_EMP_INFO                    EMPLOYEE_INFO                  SEX  
YEEXUN                         NOT_NULL_EMP_INFO              EMPLOYEE_INFO                  PHONE  
YEEXUN                         SYS_C005373                    EMPLOYEE_INFO                  HIREDATE  
YEEXUN                         UQ_EMP_INFO                    EMPLOYEE_INFO                  PHONE  
YEEXUN                         FK_EMP_INFO                    EMPLOYEE_INFO                  DEPTNO  
//我们将user_constraints视图与user_cons_columns视图连接起来   
//查看约束都指向哪些列   
column column_name format a15;  
select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status  
from user_constraints uc,user_cons_columns ucc  
where uc.table_name=ucc.table_name and  
      uc.constraint_name=ucc.constraint_name and  
      ucc.table_name='EMPLOYEE_INFO';  
--  
COLUMN_NAME     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS  
--------------- ------------------------------ --------------- --------  
EMPNO           PK_EMP_INFO                    P               ENABLED  
DEPTNO          FK_EMP_INFO                    R               ENABLED  
PHONE           NOT_NULL_EMP_INFO              C               ENABLED  
HIREDATE        SYS_C005373                    C               ENABLED  
PHONE           UQ_EMP_INFO                    U               ENABLED  
SEX             CK_EMP_INFO                    C               ENABLED  
--  
//这里有个constraint_type，他具体指下面几种类型:   
//C:check,not null   
//P:primary key   
//R:foreign key   
//U:unique   
//V:check option   
//O:read only   
--  
//我们可以通过user_tab_comments视图获得对表的注释   
select * from user_tab_comments  
where table_name='EMPLOYEE_INFO';  
TABLE_NAME                     TABLE_TYPE  COMMENTS  
------------------------------ ----------- --------------------------  
EMPLOYEE_INFO                  TABLE       information of employees  
--  
//我们还可以通过user_col_comments视图获得对表列的注释：   
select * from  user_col_comments  
where table_name='EMPLOYEE_INFO';  
--  
TABLE_NAME                     COLUMN_NAME                    COMMENTS  
------------------------------ ------------------------------ ---------------------------  
EMPLOYEE_INFO                  EMPNO                            
EMPLOYEE_INFO                  DEPTNO                           
EMPLOYEE_INFO                  ENAME                          the name of employees  
EMPLOYEE_INFO                  SEX                              
EMPLOYEE_INFO                  PHONE                            
EMPLOYEE_INFO                  ADDRESS                          
EMPLOYEE_INFO                  ID                               
EMPLOYEE_INFO                  HIREDATE   
--  
select * from user_col_comments  
where table_name='EMPLOYEE_INFO' and  
      comments is not null;  
--  
TABLE_NAME                     COLUMN_NAME                    COMMENTS  
------------------------------ ------------------------------ ------------------------  
EMPLOYEE_INFO                  ENAME                          the name of employees  
--  
//最后我们来查看一下修改后的表:   
desc employee_info;  
Name     Type         Nullable Default   Comments                
-------- ------------ -------- --------- ---------------------   
EMPNO    NUMBER(2)                                               
DEPTNO   NUMBER(3)    Y                                          
ENAME    VARCHAR2(10) Y                  the name of employees   
SEX      CHAR(2)      Y        'M'                               
PHONE    NUMBER(11)                                              
ADDRESS  VARCHAR2(50) Y                                          
ID       VARCHAR2(18) Y                                          
HIREDATE DATE                  sysdate+1  
--  
desc dept;  
Name   Type         Nullable Default Comments                 
------ ------------ -------- ------- ----------------------   
DEPTNO NUMBER(3)                                              
DNAME  VARCHAR2(10) Y                the name of department   
LOC    VARCHAR2(50) Y  
--