Oracle里面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的文章

----------------------------------------------------------------------------------------------------

 

http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!278.entry

 

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了