Oracle alter table详解

//建测试表  
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 
--