plsql/sql3-各种约束

层次/递归/分级/结构查询

如何查询总经理-部门经理-主管-员工的员工层次关系？

select Ipad(ename,length(ename)+(level*2)-2,'_') as hierarchy

from emp

start with ename='KING' and mgr is null

connect by prior empno=mgr;

如何查询scott用户的各级领导关系？

select Ipad(ename,length(ename)+(level*2)-2,'_') as hierarchy

from emp

start with ename='SCOTT'

connect by prior mgr=empno;

完整性约束：

dba-constraints这个表示存放约束的

完整性约束是一种规则，不占用任何数据库空间。完整性约束存在数据字典中，在执行sql或pl/sql期间使用，用于保证数据的完整性

not null非空约束

指定该列不允许存在空值，若不指定此约束则缺省为null

下面语句中notnull_test是所加约束的约束名称，约束类型为not null

create table test (a number constraint notnull_test not null, b char);

修改表的时候也可以添加约束

alter table dept modify (deptno constraint notnull_dept not null);

约束将保证在dept表的deptno列上不会有空值，constraint notnull_dept为用户指定约束名，不指定时系统自动生成约束名

desc dba_constraints

alter table dept drop constraint notnull_dept;

select constraint_name,constraint_type from dba_constraints where table_name='DEPT';

unique唯一键约束

在不考虑null值的情况下，指定一个列或列组为表的唯一键（即满足该表中没有任何两条记录在该列上有相同值（null除外））

在列或列组上添加unique约束后，系统将自动为该列或列组建立唯一索引

create table test (a number constraint uni_test_a unique, b number);

alter table dept add constraint uni_dept_dname unique (dname)

select constraint_name,constraint_type from dba_constraints where table_name='test';

alter table dept drop constraint uni_dept_dname;

一个列组上不能同时有重复值存在：

create table test (a number, b number, constraint uni_test_a_b unique(a,b));

alter table dept add constraint uni_dept_dname_loc unique(dname,loc)

alter table dept drop constraint uni_dept_dname_loc;

该语句定义了组合唯一键，即除null外，将保证表中不会再有任何记录在此两列中的值同时相同（但允许其中一个列有相同的情况）

primary key主键约束

在不允许null值出现的前提下，指定一个列或列组为表的主键（即在不允许该列取null值的情况下，再保证该表中无任何两行记录在该列上有相同值）

当在某列或列组上添加primary key约束后，系统将自动为该列或列组建立唯一索引，每个表只能有唯一一个primary key

create table test(a number constraint pk_test_a primary key, b number)

alter table emp add constraint pk_emp_empno primary key （empno)

insert into emp (empno,ename) values (7999,'John');

insert into emp (empno,ename) values(null,'John');

alter table emp drop constraint pk_emp_empno;

约束将保证该表以cno为主键，即保证该列not null，且在该列上不会出现两个值相同的情况

create table test(a number, b number, constraint pk_test_a_b primary key (a,b))

alter table dept add constraint pk_dept_dname_loc primary key (dname,loc);

insert into dept values(90,'SALES','CHICAGO');

alter table dept drop constraint pk_dept_dname_loc;

上例alter句中组合主键将保证表中在not null的前提下不会有任何两条记录

references完整性约束：

完整性约束命名当前表（字表）的一列或列组作为一个外部关键字，与参考表（父表）的指定主键或唯一键（两者必须存在一个）之间建立联系。通俗理解，即子表中该列内所有值必须在父表中的对应列中都有存在，父表对应列中不存在的值也不允许在子表的该列中出现，外部键与参考键可在同一表中，即父子同表。

约束名foreign key在子表中组成外部关键字的列或者列组，在表约束子句中仅仅能用此约束名，约束名reference则指定父表中组合成参考关键字的列或列组，若只指出了父表而省略了列名，则外部关键字自动参考父表的主键

alter table dept add constraint pk_dept_deptno primary key (deptno);

alter table emp add constraint fk_emp_deptno foreign key (deptno) references dept (deptno);

insert into emp (empno,ename,deptno) values （‘7000’，‘Mary',99);

然后报错了，报错的原因就是主表dept里面没有99号部门

delete from dept where deptno=20;

然后也报错了，dept删不掉的原因就是dept删掉了之后我的emp里面就没得参考了。所以删不掉

alter table emp drop constraint fk_emp_deptno;

alter table dept drop constraint pk_dept_deptno;

===

check检查点约束

该约束为表定义了一个该表必须满足的条件（null除外）

alter table emp add constraint ck_emp_sal check (Sal between 0 and 10000);

insert into emp(empno,ename,sal) values(7000,'MARY',11000);

alter table emp add constraint ck_emp_deptno check (deptno in (10,20,30,40));

alter table emp drop constraint ck_emp_sal;

alter table emp drop constraint ck_emp_deptno;

约束的校验：

Status & validated:

status=enable/disable 创建约束后是否对新插入的数据进行校验

validated=validate/novalidate创建约束时指定是否对已有数据进行校验

enable+validate校验新数据，也校验过已存在数据（默认）

enable+novalidate校验新数据，但未校验过已存在数据

disable+validate不校验新数据，但校验过已存在数据，（矛盾状态，自动转换为下种）

disable+nonvalidate新老数据都不校验（等同于约束不存在，不常用）

约束的禁用和启动

create table t1 as select * from dba_objects;

alter table t1 add constraint pk_t1 primary key (object_id);

select status,validated from dba_constraints where constraint_name='PK_T1';

添加约束，可见默认enable+validate

alter table t1 disable constraint PK_T1 cascade;

select status,validated from dba_constraints where constraint_name='PK_T1';

暂时禁用表上的相关约束，可见自动转为disable+novalidate，cascade指定暂停依赖于该主键的所有外键约束

约束的延迟校验：

create table t3 as select * from dba_objects;

alter table t3 add constraint uni_t3 unique (object_id);

select deferrable,deferred from dba_constraints where constraint_name='UNI-T3';

insert into t3 select * from t3 where object_id=(select min(object_id) from t3);

执行DML时立即进行约束校验（默认）

alter table t3 drop constraint uni_t3;

alter table t3 add constraint uni_t3 unique (object_id) deferrable initially deferred;

insert into t3 select * from t3 where object_id=(select min(object_id) from t3);

commit;执行dml时不立即进行约束校验，而是commit时才进行延迟校验