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语句违反了约束，整个提交都将失败，全军覆没。

 

转载于:https://www.cnblogs.com/jinxf/p/9166146.html