oracle alter table logging,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;

阅读(3388) | 评论(0) | 转发(0) |