oracle培训第三天

1.约束

约束是数据库能够实现业务规则以及保证数据遵循ER模型的一种手段。

约束的语法

列级约束：只能引用一个列，表中可以有多个列级约束

表级约束：引用一个或多个列，通常用来定义主键

追加约束:建表后，再通过alter table追加约束

select * from user_constraints；

select * from user_cons_columns；

五种约束的语法

非空约束

列级定义

create table student(no int not null,name varchar(40));

追加非空约束

alter table emp1 modify ename not null;

select * from user_constraints

alter table emp1 modify ename null

alter table emp1 modify ename constraint c_emp1_ename not null;

select * from user_constraints

唯一性约束

列级定义

drop table student;

create table student(no int unique,name varchar(40));

insert into student

values(null,'zzl');

insert into student

values(null,'z');

insert into student

values(1,'zzl');

insert into student

values(1,'z');

表级定义

drop table student;

create table student(no int ,name varchar(40),constraint c_uno unique(no));

追加定义

drop table student;

create table student(no int ,name varchar(40));

alter table student add constraint c_uno unique(no);

主键约束

每个表只能建立一个主键约束 primary key = unique + not null

主键列上需要索引，如果该列没有索引，则自动创建unique索引，

主键约束和唯一约束不能同时建立在一个列上

主键约束的六种写法；

列级定义

drop table student

create table student(no int primary key,name varchar(40));

drop table student

create table student(no int constraint pk_student primary key,name varchar(40));

表级定义

drop table student

create table student(no int,name varchar(40),primary key (no));

drop table student

create table student(no int,name varchar(40),constraint pk_studnet primary key (no));

追加定义

drop table student;

create table student(no int,name varchar(40));

alter table student add primary key (no);

drop table student;

create table student(no int,name varchar(40));

alter table student add constraint pk_student primary key (no);

主键和索引关联的问题

drop table student

create table student(no int ,name varchar(40));

create index index_student_no on student(no);

alter table student add constraint pk_no primary key (no);

drop table student;

create table student(no int ,name varchar(40));

create index index_student_no on student(no);

alter table student add constraint pk_no primary key (no) using index index_student_no;

alter table student drop constraint pk_no;

select * from user_indexes where table_name ='STUDENT';

create table student(no int,name varchar(40),constraint pk_studnet primary key (no) using index(create index index_student_no on student(no)

));

外键约束

作用，是为了和同一个表或其他表的主键建立连接关系，外键值必须和父表中的值匹配或者为空

外键约束和unique约束都可以有空值

外键需要参考主键的约束，但也可以参考唯一键约束

外键和主键一般分别在两个不同的表中，但也可以同处在一个表中

drop table emp1;

create table emp1 as select * from emp;

create table dept1 as select * from dept;

列级定义：

alter table dept1 add constraint pk_dept1 primary key (deptno);

create table emp2 (empno int ,deptno int references dept1(deptno),deptno2 int);

表级定义

create table emp3(empno int ,deptno int,foreign key (deptno) references dept1(deptno));

追加定义

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno);

insert into emp3 values(1,99);

insert into emp3 values(1,null);

delete from dept1 where deptno =10;

alter table emp1 drop constraint fk_emp1;

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;

delete from dept1 where deptno =10;

select * from emp1;

alter table emp1 drop constraint fk_emp1;

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete set null;

delete from dept1 where deptno =10;

select * from emp1;

check 约束

列级定义

create table emp4(empno int ,sal int check(sal>0),comm int)

表级定义

create table emp5(empno int ,sal int,comm int， check(sal>5000))

追加定义

alter table emp5 add constraint e_no_ck check(empno is not null)

insert into emp5

values(null,1,1);

check约束中的表达式中不能使用变量日期函数

alter table emp1 add constraint emp_chk_date check (hiredate < sysdate);

alter table emp1 add constraint emp_chk_date check (hiredate < to_date('2016.08.16','yyyy.mm.dd'))

级联约束

drop table test;

create table test(

pk number primary key,

fk number,

col1 number,

col2 number,

constraint fk_constraint foreign key (fk) references test,

constraint ck1 check(pk >0 and col1 >0),

constraint ck2 check(col2 > 0)

)

alter table test drop column col2;

alter table test drop column fk;

alter table test drop column pk;

alter table test drop column col1;

alter table test drop column pk cascade constraint//级联

alter table test drop column col1 cascade constraint

约束的四种状态

enable validate :无法输入违反约束的行，而且表中所有行都要符合约束

enable novalidate：表中可以存在不符合约束的状态，但对新加入数据必须符合约束条件

disable novalidate：可以输入任何数据，表中或已存在不符合约束条件的数据

disable validate：不能对表进行插入，更新，删除等操作，相当于对整个表的read only 设定。

drop table emp1;

create table emp1 as select * from emp;

update emp1 set empno =NULL where empno =7900

select * from emp1

alter table emp1 add constraint ck_emp1 check (empno is not null);

alter table emp1 add constraint ck_emp1 check (empno is not null) enable novalidate;

insert into emp1(empno)

values(null);

将disable novalidate,enable novalidate,enable validate 三种状态组合起来使用

这种组合，可以避免因有个别不符合条件的数据，而导致大数据量的传输失败

假设a表示源数据，其中有空值，b表示a表的归档表，设有非空约束，现将a表插入到b表中

alter table b modify constraint b_nnl disable ,novalidate;

insert into b select * from a;

alter table b modify constraint b_nnl enable,novalidate;

update b set channel = 'NOT KNOWN' where channel is null;

alter table b modify constraint b_nnl enable ,validate;

延迟约束

alter table emp1 add constraint chk_sal check(sal > 500) deferrable;//延迟约束

insert into emp1(empno,sal)

values(3030,100);

set constraint chk_sal immediate;

insert into emp1(empno,sal)

values(3030,100)

set constraint chk_sal deferred;//延迟

insert into emp1(empno,sal)

values(3030,100)

alter table emp1 drop constraint chk_sal;

alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially immediate;

insert into emp1(empno,sal)

values(3030,100);

alter table emp1 drop constraint chk_sal;

alter table emp1 add constraint chk_sal check(sal > 500) deferrable initially deferred;

insert into emp1(empno,sal)

values(3030,100);