10.1 、Oracle 约束的DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE 和 INITIALLY DEFERRED

1. 约束的 DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE 和 INITIALLY DEFERRED

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

2. DEFERRABLE (可以推迟的约束检查):可以推迟到事务结尾. 目前仅UNIQUE, PRIMARY KEY, EXCLUDE和FOREIGN KEY才支持DEFERRABLE. NOT NULL和CHECK约束不支持.
   NOT DEFERRABLE (不可推迟的约束检查):在每一个命令后会立即验证是否满足约束条件. 缺省是NOT DEFERRABLE.
   如果约束是INITIALLY IMMEDIATE,那么每条语句之后就检查它. 这个是缺省.
   如果约束是INITIALLY DEFERRED,那么直到事务完成才检查.

检查的时间可以用以下命令修改:

SET CONSTRAINTS [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

例子:

默认是NOT DEFERRABLE INITIALLY IMMEDIATE
    create table t1 (id number, name char(10));
    alter table t1 modify id number primary key;
    SQL> insert into t1 values(1,'Tough1');
    已创建 1 行.
    SQL> insert into t1 values(1,'Tough1');
    insert into t1 values(1,'Tough1')

    第 1 行出现错误:
    ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005428)
    
DEFERRABLE 或I NITIALLY DEFERRED 的情况:
    create table t2 (id number, name char(10));
    alter table t2 modify id number primary key INITIALLY DEFERRED;
    或
    alter table t2 modify id number primary key DEFERRABLE INITIALLY DEFERRED;
    SQL> insert into t2 values(1,'Tough1');
    已创建 1 行.
    SQL> insert into t2 values(1,'Tough1');
    已创建 1 行.
    SQL> commit;
    commit

    第 1 行出现错误:
    ORA-02091: 事务处理已回退
    ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005435)

5种约束:
   NOT NULL     非空 NN
   UNIQUE       唯一 UK
   PRIMARY KEY  主键 PK
   FOREIGN KEY  外键 FK
   CHECK        条件 CK

约束创建:
   1) 创建对象时定义
   2) 创建对象后定义
   3) 约束有名字:默认:SYS_C0, 系统推荐:表名_列名_约束类型

   create table scott.tab
   drop table scott.me_stu purge;
   create table scott.me_stu(
      id    varchar2(20) constraints stu_id_pk  primary key,
      nam   varchar2(32) constraints stu_nam_uk unique,
      age   number(10)   constraints stu_sal_ck check(age>0),
      notes varchar2(32) constraints stu_not_nn not null
   ) compress nologging;
    或
   drop table scott.me_stu purge;
   create table scott.me_stu(
      id    varchar2(20),
      nam   varchar2(32),
      age   number(10) ,
      notes varchar2(64),
      constraints stu_id_pk   primary key(id),
      constraints stu_nam_uk  unique(nam),
      constraints stu_sal_ck  check(age>0),
      constraints stu_notes_nn check(notes is not null)
   ) compress nologging;
   alter table scott.me_stu move compress;

--外键
--参照完整性约束
--限制（Restrict）。不允许进行修改或删除操作。若修改或删除主表的主键时,如果子表中存在子记录,系统将产生一个错误提示。这是缺省的参照完整性设置。
--置空(Set Null)。如果外键列允许为空,若修改或删除主表的主键时,把子表中参照的外键列设置为空值（NULL）。
--置为缺省(Set Default)。如果指定了缺省值,若修改或删除主表的主键时,把子表中参照的外键设置为缺省值（Default）。
--级联(Cascade)。把主表中主键修改为一个新的值时,相应修改子表中外键的值；或者删除主表中主键的记录时,要相应删除子表中外键的记录。
   drop table scott.me_score purge;
   create table scott.me_score (
      sid    varchar2(20) constraint score_sid_fk references scott.me_stu(id),
      --constraint score_sid_fk references scott.me_stu(id) on delete cascade,
      cid    varchar2(20),
      score  number(10,2)
   ) nologging;
    或
   drop table scott.me_score purge;
   create table scott.me_score (
      sid    varchar2(20),
      cid    varchar2(20),
      score  number(10,2),
      --constraint score_sid_fk foreign key(sid) references scott.me_stu(id)
      constraint score_sid_fk foreign key(sid) references scott.me_stu(id) on delete cascade
   ) nologging;

--查询约束
   select * from dba_constraints  where table_name like '%me_stu%';
   select * from dba_cons_columns where table_name like '%me_stu%';

--删除约束
   alter table scott.me_stu   drop constraints stu_nam_uk;
   alter table scott.me_score drop constraints score_sid_fk;

--添加约束
   alter table scott.me_stu modify(nam   varchar2(32) constraint stu_nam_nn unique);
   alter table scott.me_stu modify(notes varchar2(32) constraint stu_not_nn not null);
   alter table scott.me_stu   add constraint stu_nam_uk unique(nam);
   alter table scott.me_score add constraint score_sid_fk foreign key(sid) references scott.me_stu(id) on delete cascade;

--外键对DML影响
INSERT:
   1) 子表对父表无影响
   2) 插入子表外键时,父表必须存在

DELETE:
   1)对子表DELETE操作对父表无影响
   2)对父表DELETE操作须先对子表删除

UPDATE:
   1) 对父表和子表都有影响
   2) 对父表如果子表引用则不能更新
   3) 对子表UPDATE操作时,外键在父表必须存在

--外键对DDL影响
   1) 对子表没影响,对父表如果子表引用则不能删除

--为了消除影响可以在建立约束时添加子句:
   on delete set null 主表删除时,子表置空
   on delete cascade  主表删除时,子表级联删除

--禁用与启用约束
   alter table scott.me_score disable constraint score_sid_fk;
   alter table scott.me_score enable  constraint score_sid_fk;

--约束(constraint)状态:
   ENABLED/DISABLED
   VALIDATED/NOVALIDATED
   DEFERRABLE/NON-DEFERRABLE
   DEFERRED/IMMEDIATE
   RELY/NORELY

1) DEFERRABLE/NON-DEFERRABLE,DEFERRED/IMMEDIATE
   deferrable:     constraint如果被定义成deferrable那么constraints可以在deferred和imediate两种状态相互转换
   not deferrable: constraint默认是not deferrable,同initially immediate,不能在deferred和imediate两种状态相互转换
   deferred:       意味着constraint将被延迟即在transaction过程中使constraint失效,等到如果transaction commit时transaction会变成immediate
   immediate:      意味着constraint在transaction过程中使constraint一直有效
   deferrable initially immediate: 允许将constraint再改为initially deferred
   deferrable initially deferred:  允许将constraint再改为initially immediate
   
   drop table scott.test purge;
   create table scott.test(    
      x number constraint check_x check (x > 0) deferrable initially immediate,
       y number constraint check_y check (y > 0) deferrable initially deferred
   )nologging;
   或
   alter table scott.test add constraint check_x (x > 0) deferrable initially immediate;
   alter table scott.test add constraint check_y (y > 0) deferrable initially deferred;
   insert into scott.test values ( 1,1 );
   commit;

--initially immediate:在transaction过程中使constraint一直有效
   insert into scott.test values (-1,1 );
   
   ERROR at line 1:
   ORA-02290: check constraint (SCOTT.CHECK_X) violated

--转换initially immediate=>deferred
   set constraint scott.check_x deferred;
   insert into scott.test values (-1,1 );
   1 row created.
   commit;
   
   ERROR at line 1:
   ORA-02091: transaction rolled back
   ORA-02290: check constraint (SCOTT.CHECK_X) violated

--initially deferred:constraint被延迟,transaction commit时transaction会变成immediate
   insert into scott.test values ( 1,-1 );
   1 row created.
   commit;
   
   ERROR at line 1:
   ORA-02091: transaction rolled back
   ORA-02290: check constraint (SCOTT.CHECK_Y) violated

--转换initially deferred=>immediate
   set constraint scott.check_y immediate;
   insert into scott.test values ( 1,-1 );
   
   ERROR at line 1:
   ORA-02290: check constraint (SCOTT.CHECK_Y) violated

1) enable/disable validate/novalidate
   enable/disable:      对未来的数据有约束/无约束
   validate/novalidate: 对已有的数据有约束/无约束

    启用约束:
        enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束.
        enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.
    
    禁用约束:
        disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
        disable validate :关闭约束,删除索引,不能对表进行 插入/更新/删除等操作.

注意:如果加约束到一个大表,那么ORACLE会LOCK这个表,然后SCAN所有数据,来判断是否符合CONSTRAINT的要求,在繁忙的系统里显然是不合适的。所以用enable、
novalidate比较合适,因为ORACLE仅仅会LOCK表一小段时间来建立CONSTRAINT,当CONSTRAINT建立后再VALIDATE,这时检验数据是不会LOCK表
    alter table scott.me_score disable validate constraint score_sid_fk;
    alter table scott.me_score enable  novalidate constraint score_sid_fk;