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';  实践： Oracle： 1、添加主键约束： ALTER TABLE "DSP_LETTER_DISPATCH_BILL" ADD CONSTRAINT pk_dsp_letter_dispatch_bill PRIMARY KEY ("DISPATCH_SERNO") USING INDEX TABLESPACE "PIS_INDEX"  ENABLE; 添加过主键约束后，oracle会自动创建同名的唯一索引： CREATE UNIQUE INDEX "TEST_PIS_PRODUCT"."PK_DSP_LETTER_DISPATCH_BILL" ON "TEST_PIS_PRODUCT"."DSP_LETTER_DISPATCH_BILL" ("DISPATCH_SERNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING TABLESPACE "PIS_INDEX" ; 2、删除主键约束： ALTER TABLE DSP_LETTER_DISPATCH_BILL DROP CONSTRAINT pk_dsp_letter_dispatch_bill; 删除主键约束后，oracle会自动删除同名的唯一索引； PostageSQL： 1、添加主键约束： alter table dsp_letter_dispatch_bill add constraint pk_dsp_letter_dispatch_bill primary key(dispatch_serno); 添加过主键约束后，需要手工添加唯一索引： CREATE UNIQUE INDEX idx_dsp_letter_dispatch_bill ON dsp_letter_dispatch_bill (dispatch_serno) TABLESPACE pis_index; 2、删除主键约束： ALTER TABLE DSP_LETTER_DISPATCH_BILL DROP CONSTRAINT pk_dsp_letter_dispatch_bill; 删除主键约束后，需要手工删除唯一索引； drop index idx_dsp_letter_dispatch_bill;