oracle alter table 详解

海宝3279

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