Constraint基础概念
一直对constraint的概念比较模糊,没有系统得学习过一次。这次专门来学习一下这方面的内容。其实如果不用到REF constraint的话,这部分还是比较简单明晰的,关键是要记住创建和修改constraint的几个语法,这很重要。
首先来看一下《SQL Reference》中对于Constraint的说明:
下面说一下我的认识:
1、Constraints的目的:
设立Constraint就是为了让数据满足某些规则。
2、Constraint的类型:
not null (不能为空)
unique (值必须唯一)
primary key (not null + unique)
goreign key (该表值必须在外键表中存在)
check (自己加的条件)
ref (不熟)
注:Constraints不但可以建立在Table上,也可以建立在View上。
3、Constraint的状态:
① Deferrable
该参数用于指定是否可以是同set语句来进行临时控制constraint,时约束在commit时才生效
DEFERRABLE:可以使用set constraint字句
NOT DEFERRABLE:不可以使用set constraint字句(默认)
② Initially
该参数用于建立默认的DEFERRABLE类型约束
INITIALLY一般都要和IMMEDIATE、DEFERRED一起使用
INITIALLY IMMEDIATE:在执行SQL时违反约束即报错(默认)
INITIALLY DEFERRED:在提交时才报错
③ Validate | NoValidate
该参数一般与Enabled和Disabled属性搭配使用
④ Enable
该参数确认约束应用于数据
ENABLE VALIDATE:将验证已经存在的和之后的操作是否符合约束(默认)
ENABLE NOVALIDATE:不验证已经存在的数据,但对之后进行的操作有效
⑤ Disable
该参数使约束失效
DISABLE VALIDATE:约束失效标注,可用于暂时导入大量数据时,不进行索引更新
DISABLE NOVALIDATE:约束失效,并不保证约束是否正确,即不保证已有数据满足约束(默认)
⑥ Rely
Rely和Norely只能用在 ALTER TABLE MODIFY constraint 语句中
Rely:告诉Oracle,不必对NOVALIDATE模式的约束的数据进行信任,即需要检验以前的数据
(这个没用过,实在搞不准确切含义,还是把文档的内容直接放上来)
4、set语句
----------------------------------------------------------------------------------------------------
转一篇Constraint的文章
----------------------------------------------------------------------------------------------------
constraints 三个需要注意的地方
1. deferrable
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换。deferred只在transaction中有效,也就是只可以在transaction过程中使constraint失效,但如果transaction commit的话,transaction会变成immediate。
SQL> create table cons_parent (id number(10),name varchar2(10));
Table created.
SQL> create table cons_child (id number(10),name varchar2(10));
Table created.
SQL> alter table cons_parent add primary key (id);
Table altered.
SQL>alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id);
Table altered.
SQL> alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id);
Table altered.
一个constraint默认是NOT DEFERRABLE的
SQL> select constraint_name||' '||deferrable from all_constraints
2 where constraint_name='CHI_FK_PAR';
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR NOT DEFERRABLE
NOT DEFERRABLE的不能在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par deferred;
SET constraints chi_fk_par deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
SQL> alter table cons_child drop constraints chi_fk_par;
Table altered.
SQL> alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id) deferrable;
Table altered.
SQL> select constraint_name||' '||deferrable from all_constraints
2 where constraint_name='CHI_FK_PAR';
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR DEFERRABLE
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par immediate;
Constraint set.
SQL> insert into cons_child values (2,'llll')
insert into cons_child values (2,'llll')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
SQL> set constraints chi_fk_par deferred;
Constraint set.
SQL> insert into cons_child values (2,'llll');
1 row created.
SQL> set constraints chi_fk_par immediate;
SET constraints chi_fk_par immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferred只在transaction中有效,也就是只可以在transaction过程中使constraint失效,但如果transaction commit的话,transaction会变成immediate。
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferrable会影响CBO的计划,并且正常情况下没有应用的必要,所以建议不要修改,而用系统默认的non deferrable
2. enable/disable validate/novalidate
enable/disable对未来的数据有约束/无约束。
validate/novalidate对已有的数据有约束/无约束。
如果加约束到一个大表,那么ORACLE会LOCK这个表,然后SCAN所有数据,来判断是否符合CONSTRAINT的要求,在繁忙的系统里显然是不合适的。所以用enable novalidate比较合适,因为ORACLE仅仅会LOCK表一小段时间来建立CONSTRAINT,当CONSTRAINT建立后再VALIDATE,这时检验数据是不会LOCK表的。
这方面很多书上都有例子,就不在这里累述了
3.REFERENCE 让人疑惑的地方
SQL> create table wwm_father (id number,name varchar2(10),primary key (id,name))
Table created.
SQL> create table wwm_child (id number,name varchar2(10),
2 foreign key (id,name) references wwm_father on delete set null);
Table created.
SQL> insert into wwm_father values (6,'wwm');
1 row created.
SQL> insret into wwm_child values (6,'fff');
SP2-0734: unknown command beginning "insret int..." - rest of line ignored.
可以看出,REFERENCE是起作用的。但下面就有点让人疑惑了,似乎ORACLE不用该用这种策略来做,
SQL> insert into wwm_child values (6,null);
1 row created.
SQL> insert into wwm_child values(null,'lll');
1 row created.
SQL> insert into wwm_child values (null,null);
1 row created.
SQL> select * from wwm_father;
ID NAME
---------- --------------------
6 wwm
SQL> select * from wwm_child;
ID NAME
---------- --------------------
6
lll
SQL> select count(*) from wwm_child;
COUNT(*)
----------
3
可见,如果向CHILD表插入NULL的话,ORACLE默认认为NULL是匹配FATHER表里相关的REFERENCE的字段内容的。因此FOREIGN KEY的COLUMN大家就需要认真考虑是否要设置成NOT NULL了