【十二】约束

12.1 什么是约束

1）约束的作用：

约束是数据库能够实施业务规则以及保证数据遵循[实体--关系]模型的一种手段。如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。

2）约束的语法：

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

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

追加定义：建表后，再通过alter table命令追加的约束；

查看约束的两个数据字典视图：

select * from user_constraints;select * from user_cons_columns;

12.2 五种约束的建立

12.2.1非空约束

列级定义：

create table t1 (id number(2) not null, name varchar2(4));

追加非空约束：

如：alter table t1 modify ename not null;或（alter table t1 modify ename constraint aaa not null）。

注意：非空约束没有表级定义。

12.2.2唯一性约束

1）unique不包括空值的唯一性；

2）单列可设unique + not null约束，约束之间没有“，”；

create table a (id int unique not null, name char(10));

3）单表中unique约束没有数量限制；

4）unique约束的列上有索引。

列级定义：

create table t1(id number(2) unique,name varchar2(4));

表级定义：

create table t2(id number(2),name varchar2(4),constraint id_uk unique(id));

追加定义：

alter table t2 add CONSTRAINT id_uk UNIQUE (id);

12.2.3主键约束

主键约束语法上与唯一约束类似

1）每个表只能建立一个主键约束，primary key = unique key + not null，主键约束可以是一列，也可以是组合多列。

2）主键列上需要索引，如果该列没有索引会自动建立一个unique index，如果该列上已有索引（非唯一也可以），那么就借用这个索引，当主键约束被删除后借用的索引不会被删除。同理，多列组合的主键，需要建立多列组合索引，而多列主键的单列上还可以另建单列索引。

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

4）主键约束和外键约束可以在同一列上。（典型的例子是一个表上的两列是组合主键，但这两列的每列都是其他表的外键）

关于主键和索引关联的问题:

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;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)));

12.2.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> 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子句

报错：ORA-02292: integrity constraint (SCOTT.E_FK) violated - child record found

删除外键约束，使用ON DELETE CASCADE关键字重建外键：

SQL>alter table emp1 drop constraint fk_emp1;

SQL>ALTER TABLE emp1 ADD CONSTRAINT fk_emp1 FOREIGN KEY(deptno) REFERENCES dept1(deptno) ON DELETE CASCADE;

视图delete_rule列会显示CASCADE，否则显示NO ACTION

SQL>select constraint_name,constraint_type,status,delete_rule from user_constraints;

测试：

SQL>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;

报错：ORA-02449: 表中的唯一主键被外键引用

SQL> drop table dept1 cascade constraint purge;

注意：这时外键约束也被删除了。

12.2.5.CHECK约束

1、作用：CHECK约束是检查一个表达式，表达式的逻辑结果为”F“时，违反约束则语句失败。

表达式使用”( )“扩起来

涉及多列的表达式，只能使用表级定义描述。

2、语法:

列级定义

SQL> create table emp1 (empno int,sal int check (sal>500),comm int);

表级定义

SQL> create table emp2 (empno int,sal int,comm int,check(sal>500));

追加定义

SQL> alter table emp2 add constraint e_no_ck check (empno is not null);

验证

SQL> insert into emp2 values(null,1,1);

报错：ORA-02290: 违反检查约束条件 (SCOTT.E_NO_CK)

3、check约束中的特殊情况

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

SQL> alter table emp1 add constraint chk check(hiredate<sysdate);

报错：ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误

SQL> alter table emp1 add constraint chk check(hiredate<to_date('2000-01-01','yyyy-mm-dd')); 

这句是可以的。

2）级联约束

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))；

当删除列时, 看看会发生什么?

SQL> ALTER TABLE test2 DROP (col1);

这句不能执行，在constraint ck1 中使用了该列。

如果一定要删除级联约束的列，带上cascade constraints才行

SQL> ALTER TABLE test2 DROP (col1) cascade constraints;

3）check中可以使用in表达式，如(check deptno in(10,20))

12.3 约束的四种状态

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

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

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

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

（如是主键，会删除索引，当enable后，又建立了索引）

更改约束状态是一个数据字典更新，将对所有session有效。

1）测试enable novalidate 这种组态的用法

常用于当在表中输入了一些测试数据后，而上线后并不想去清除这些违规数据，但想从此开始才执行约束。

假设已经建立了一个emp1表，也插入了数据，如果有一天想在empno上加入primary key 但是之前有不符合（not null+unique)约束的，怎样才能既往不咎呢？

SQL>create table emp1 as select * from emp; 没有约束考过来SQL>update emp1 set empno=7788 where empno=7369; 设置一个重值SQL>alter table emp1 add constraint pk_emp1 primary key (empno); 因要检查主键唯一性，拒绝建立此约束SQL>alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate; 这句话也不行，原因是唯一索引在捣乱SQL>create index empno_index on emp1(empno); 建一个普通索引不受unquie的限制SQL>alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;。

从此之后，这个列的DML操作还是要符合主键约束（not null+unique)。

2）将disable novalidate，enable novalidate和enable validate三种状态组合起来的用法：

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

假设有a表是源数据表，其中有空值，b表是a表的归档表，设有非空约束，现要将a表数据（远程）大批量的插入到b表（本地）。

SQL>alter table b modify constraint b_nn1 disable novalidate; 先使B表非空约束无效SQL>insert into b select * from a; 大批数据可以无约束插入，空值也插进B表里了SQL>alter table b modify constraint b_nn1 enable novalidate; 既往不咎，但若新输入数据必须符合要求SQL>update b set channel='NOT KNOWN'where channel is null; 将所有空值填充了，新老数据都符合要求了SQL>alter table b modify constraint b_nn1 enable validate; 最终是约束使能+验证生效，双管齐下

12.4 延迟约束

12.4.1作用：

在插入大批量数据时先不检查约束（节约时间），而当提交时集中在一起检查约束。注意：一旦有一条DML语句违反了约束，整个提交都将失败，全军覆没。

延迟（deferrable）可以通过查询User_Constraints视图获得当前所有关于约束的系统信息。

查看user_constraints中的两个字段：

Deferrable：是否为延迟约束[Deferrable / Not Deferrable(缺省)]

Deferred：是否采用延迟[Immediate(缺省) / Deferred]

这两个字段的关系是：

1）如果创建约束时没有指定Deferrable 那么以后也无法使约束成为延迟约束（只有通过重建约束时再指定它是延迟约束）；

2）一个约束只有被定义成Deferrable，那么这个约束session级才可以在deferred和immediate两种状态间相互转换。

12.4.2延迟约束示例：

SQL> alter table emp1 add constraint chk_sal check(sal>500) deferrable; 建立可延迟约束

已将chk_sal 约束设为Deferrable了，下面可有两种面向session的方案：

1）约束不延迟，插入数据立刻检查约束

SQL>set constraint chk_sal immediate;

2）约束延迟，提交时将整个事务一起检查约束

SQL>set constraint chk_sal deferred;

使用set constraint切换只影响当前会话。

也可以在建立约束时一次性指定系统级(全局设定所有会话）的延迟约束

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

小伙伴们，自己动手试试吧[呲牙]！

the end ！！！

@jackman 共筑美好！