oracle之约束
约束
12.1 什么是约束
约束是数据库能够实施业务规则以及保证数据遵循实体--关系模型的一种手段。
考点：如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。
12.2 约束的语法：
列级定义：只能引用一个列，表中可以有多个列级约束。
表级定义：引用一个或多个列，通常用来定义主键。
追加定义：建表后，再通过alter table命令追加的约束。
查看约束的两个数据字典视图
select * from user_constraints;
select constraint_name,column_name,table_name from user_cons_columns;
12.3 五种约束的语法
12.3.1非空约束
列级定义：
create table stud (id number(2) not null, name varchar2(4))
追加非空约束：
not null约束比较特殊，一般只是列级定义和表外定义，当使用表外（追加）时，要使用modify关键字（考点）
如：alter table emp1 modify ename not null;
或 alter table emp1 modify ename constraint xyz not null;
12.3.2.唯一性约束
列级定义
create table a1(id number(2) unique,name varchar2(4));
表级定义
create table a2(id number(2),name varchar2(4),constraint id_uk unique(id));
追加定义
alter table a2 add CONSTRAINT id_uk UNIQUE (id);
考点：
1）unique不包括空值的唯一性2）单列可设unique+not null约束，约束之间没有“，”create table a (id int unique not null, name
char(10) unique not null);
3）单表中unique约束没有数量限制。
4）unique约束的列上有索引。
12.3.3 主键约束
主键约束语法上与唯一约束类似
考点：
1）每个表只能建立一个主键约束，primary key=unique key + not null，主键约束可以是一列，也可以是组合多列。
2）主键列上需要索引，如果该列没有索引会自动建立一个unique index, 如果该列上已有索引（非唯一也可以），那么就借用这个索引，由于是借用的索引，当主键约束被删除后，借用的索引不会被删除。同理，多列组合的主键，需要建立多列组合索引，而多列主键的单列上还可以另建单列索引。
3）主键约束和唯一约束不能同时建立在一个列上。
关于主键和索引关联的问题:（这个地方考点较多)
SQL>
create table t (id int, name char(10));
insert into t values (1, 'sohu');
insert into t values (2, 'sina');
commit;
SQL> create index t_idx on t(id);
下面这两句话是一样的效果，因为缺省情况下id列已经有索引t_id了，建主键时就会自动用这个索引（考点）。
SQL> alter table t add constraint pk_id primary key (id);
SQL> alter table t add constraint pk_id primary key (id) using index t_idx; [如果该列已有索引]
SQL> select CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME from user_constraints;
CONSTRAINT_NAME TABLE_NAME INDEX_NAME
------------------------------ ------------------------------ ------------------------------
FK_DEPTNO EMP
PK_DEPT DEPT PK_DEPT
PK_EMP EMP PK_EMP
PK_ID T T_IDX
SQL> alter table t drop constraint pk_id; //删除了约束，索引还在，本来就是借用的索引。
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP
PK_DEPT
T_IDX
SQL> drop table t purge; //t_idx是和t表关联的，关键字purge使表和索引一并永久删除了。
也可以使用using子句在建表、建约束、建索引一条龙下来，当然primary key 也会自动使用这个索引(考点）。删除该约束，索引还存在。
SQL> create table t (id int,name char(10),constraint pk_id primary key(id) using index (create index t_idx on t(id)));
SQL> select CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME from user_constraints;
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP
PK_DEPT
T_IDX
12.3.4.外键约束 （引用完整性约束）
作用：是为了和同一个表或其他表的主关键字(或唯一关键字)建立连接关系，外键值必须和父表中的值匹配或者为空值。
考点：
1）外键约束和unique约束都可以有空值。
2）外键需要参考主键约束，但也可以参考唯一键约束。
3）外键和主键一般分别在两个表中，但也可以同处在一个表中。
SQL> create table emp1 as select * from emp;
SQL> create table dept1 as select * from dept;
列级定义
SQL> alter table dept1 add constraint pk_dept1 primary key(deptno);
SQL> create table emp100 (empno int,deptno int references dept1(deptno),deptno2 int);
//外键的列级定义有点特殊，使用references 不使用foreign key关键字。
表级定义
SQL> create table emp200 (empno int,deptno int,sal int,foreign key(deptno) references dept1(deptno));
追加定义
ALTER TABLE emp1 ADD CONSTRAINT fk_emp1 FOREIGN KEY(deptno) REFERENCES dept1(deptno);
主外键构成完成性约束，有两个要点：
1）插入或更新有外键的记录时，必须参照主键，（子要依赖父）。
2）有外键表时，主键表的记录不能做DML删除，（父不能舍子），
如果一定要删除, 需要在建立外键时加
on delete cascade子句
或
on delete set null子句
关于ON DELETE CASCADE子句
测试：
delete from dept1 where deptno=30
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.E_FK) violated - child record found
删除外键约束
SQL>alter table emp1 drop constraint fk_emp1;
使用ON DELETE CASCADE关键字重建外键,
SQL>ALTER TABLE emp1 ADD CONSTRAINT fk_emp1 FOREIGN KEY(deptno) REFERENCES dept1(deptno) ON DELETE CASCADE
SQL>select constraint_name,constraint_type,status,delete_rule from user_constraints;
注意: delete_rule列会显示CASCADE,否则显示NO ACTION(考点)
测试：
delete from dept1 where deptno=30
再查看emp1表的deptno已经没有30号部门了，如果再对dept1的操作进行rollback，emp1的子记录也随之rollback
ON DELETE CASCADE要慎用，父表中删除一行数据就可能引发子表中大量数据丢失。
为此，还有on delete set null子句，顾名思义是子表不会删除（丢失）记录，而是将外键的值填充null。
如果disable dept1主键约束并使用级联cascade关键字，则emp1的外键也会disable, 若再次enable dept1主键，则emp1外键任然保持disable.
SQL> alter table dept1 disable constraints pk_dept1 cascade;
SQL> alter table dept1 enable constraints pk_dept1;
SQL>select constraint_name,constraint_type,status,delete_rule from user_constraints;
SQL> drop table dept1 purge;
drop table dept1 purge
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
SQL> drop table dept1 cascade constraint purge;
注意：这时外键约束也被删除了（考点）
12.3.5.CHECK约束
列级定义
SQL> create table emp100 (empno int,sal int check (sal>0),comm int);
表级定义
SQL> create table emp200 (empno int,sal int,comm int,check(sal>1000));
追加定义
SQL> alter table emp200 add constraint e_no_ck check (empno is not null);
验证
SQL> insert into emp200 values(null,1,1);
insert into emp200 values(null,1,1)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.E_NO_CK)
12.3.6 check约束中的表达式中不能使用变量日期函数（考点）。
SQL> alter table emp1 add constraint chk check(hiredate<sysdate);
alter table emp1 add constraint chk check(hiredate<sysdate)
*
第 1 行出现错误:
ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误
SQL> alter table emp1 add constraint chk check(hiredate<to_date('2000-01-01','yyyy-mm-dd')); //这句是可以的
12.3.7 级联约束（考点）
测试表
CREATE TABLE test2 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test2,
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0)
)
/
当删除列时, 看看会发生什么?
ALTER TABLE test2 DROP (col2); //这句可以执行
ALTER TABLE test2 DROP (fk); //这句可以执行
ALTER TABLE test2 DROP (pk); //这句不能执行，在constraint ck1 中使用了该列，约束级联问题
ALTER TABLE test2 DROP (col1); //这句不能执行，在constraint ck1 中使用了该列，约束级联问题
如果一定要删除级联约束的列，带上cascade constraints才行
ALTER TABLE test2 DROP (pk) cascade constraint; //所有与pk列有关的约束统统随该列被删掉。
或(ALTER TABLE test2 DROP (col1) cascade constraints;)
CASCADE CONSTRAINTS 将丢弃在删除列上的唯一键或主键约束。
//注意：constraint ck1约束只能是表级约束（为什么？）
12.3.8 约束的四种状态
enable validate :无法输入违反约束的行，而且表中所有行都要符合约束
enable novalidate :表中可以存在不合约束的状态，,但对新加入数据必须符合约束条件.
disable novalidate :可以输入任何数据，表中或已存在不符合约束条件的数据.
disable validate :不能对表进行插入/更新/删除等操作,相当于对整个表的read only设定.
更改约束状态是一个数据字典更新，将对所有session有效。
举例：
1)enable novalidate 这种组态的用法
常用于当在表中输入了一些测试数据后﹐而上线后并不想去清除这些违规数据﹐但想从此开始才执行约束。
假设已经建立了一个emp1表，也插入了数据，如果有一天想在empno上加入primary key 但是之前有不符合（not null+unique)约束的，怎样才能既往不咎呢？
create table emp1 as select * from emp;(CTAS语句使约束并没有考过来）
update emp1 set empno=7788 where empno=7369;(设置一个重号)
alter table emp1 add constraint pk_emp1 primary key (empno);因要检查主键唯一性，拒绝建立此约束。
alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;（这句话也不行，为什么？原因是唯一索引在捣乱）
create index empno_index on emp1(empno);建一个索引，一定要一个普通索引，不能是唯一索引，普通索引不受unquie的限制）
alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;（这句话可以了）。
从此之后，这个列的DML操作还是要符合（not null+unique)。
2) disable validate组态的特点：
SQL> alter table emp1 add constraint pk_emp1 primary key(empno);
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP1
PK_EMP
PK_DEPT
SQL> alter table emp1 modify constraint pk_emp1 disable validate; //主键索引将自动删除（考点）
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP
PK_DEPT
SQL> update emp1 set sal=8000;
update emp1 set sal=8000
*
第 1 行出现错误:
ORA-25128: 不能对带有禁用和验证约束条件 (SCOTT.PK_EMP1) 的表进行插入/更新/删除
SQL> alter table emp1 modify constraint pk_emp1 enable validate;
表已更改。
SQL> select index_name from user_indexes;
INDEX_NAME
------------------------------
PK_EMP1
PK_EMP
PK_DEPT
3)将disable novalidate，enable novalidate和enable validate三种状态组合起来的用法：
这种组合，可以避免因有个别不符合条件的数据而导致大数据量的传输失败。
假设有a表是源数据表，其中有空值，b表是a表的归档表，设有非空约束,现要将a表数据（远程）大批量的插入到b表（本地）。
alter table b modify constraint b_nn1 disable novalidate; //先使B表非空约束无效。
insert into b select * from a; //大批数据可以无约束插入，空值也插进B表里了。
alter table b modify constraint b_nn1 enable novalidate; //既往不咎，但若新输入数据必须符合要求。
update b set channel='NOT KNOWN'where channel is null; //将所有空值填充了，新老数据都符合要求了。
alter table b modify constraint b_nn1 enable validate; //最终是约束使能+验证生效，双管齐下。
12.3.9 延迟约束
可延迟（deferrable）可以通过查询User_Constraints视图获得当前所有关于约束的系统信息.
查看user_constraints中的两个字段
Deferrable //是否为延迟约束 值为:Deferrable或Not Deferrable(缺省）.
Deferred //是否采用延迟 值为:Immediate（缺省）或Deferred.
关于Deferrable 可延迟，提醒以下几点：
1）约束的默认方式下是：enable/validate 和Not Defferrable。
2）如果创建约束时没有指定deferrable 那么无法在后来使约束成为延迟约束（只有通过重建约束时再指定它是延迟约束）
3）一个约束只有被定义成deferrable，那么这个约束session级才可以在deferred和immediate两种状态间相互转换
例：
SQL> alter table emp1 add constraint chk_sal check(sal>500) deferrable;
已将chk_sal 约束设为可延迟了，下面可有两种面向session的方案：
1）约束不延迟，插入数据立刻检查约束SQL>set constraint chk_sal immediate;
2）约束延迟，提交时将整个事务一起检查约束
SQL>set constraint chk_sal deferred;
也可以在建立约束时一次性指定系统级的延迟约束
alter table emp1 add constraint chk_sal check(sal>500) deferrable initially immediate;
或
alter table emp1 add constraint chk_sal check(sal>500) deferrable initially deferred;
考点：
1）使用set immediate和deferred的切换只影响当前会话，而initially状态将应用于所有会话。
2）延迟约束时，一旦有一条DML语句违反了约束，整个提交都将失败，全军覆没。
