oracle 约束(constraint)的几个参数的小研究

转载： http://blog.csdn.net/kywinder/article/details/5282619

首先搞清下几个概念：
ORACLE中，约束分deferred 跟 immediate 2种：
  deferred:如果 Oracle 在事务提交（commit）时才对约束执行检查，则称此约束是延迟的（deferred）。如果数据违反了延迟约束，提交操作将导致事务被回滚（undo）。 
  immediate:如果约束是即时的（immediate）（非延迟的），则此约束将在语句执行结束后进行检查。如果数据违反了延迟约束，语句将被立即回滚。

一般情况下，我们用的约束初始都是immediate型的(默认)，而且不好转为deferred型。但是如果初始是deferrable(需要手动指定),那deferred跟immediate 2种状态可以随意转换。
此外，约束有以下4种状态：
  ENABLE（启用）确保所有输入的数据都遵从约束（constraint） 
  DISABLE（禁用）总是允许输入数据，无论数据是否遵从约束 
  VALIDATE（验证）确保已存在的数据遵从约束 
  NOVALIDATE（无验证）允许已存在的数据不遵从约束

ENABLE VALIDATE 与 ENABLE 相同。Oracle 将检查约束，并保证所有数据均遵从约束。
ENABLE NOVALIDATE 表示所有新插入或被修改的数据都必须遵从约束，但允许已存在的数据不遵从约束。
DISABLE NOVALIDATE 与 DISABLE 相同。Oracle 不会检查约束.
DISABLE VALIDATE 将禁用约束，移除约束使用的索引，并禁止修改约束键的数据。

约束不论哪种类型，要能够生效，必须状态是enable才行。

SQLPLUS(oracle 10.2.0.1):

--deferred 跟 immediate的对比试验-----------------------------
SQL >  drop  table  aa  purge;

Table  dropped .

SQL >  create  table  aa (  id  number , name  varchar2( 20 ), constraint  pk  primary  key( id));

Table  created .

SQL >  col  constraint_name  for  a11
SQL >  select  CONSTRAINT_NAME  , CONSTRAINT_TYPE , TABLE_NAME , STATUS , DEFERRABLE , DEFERRED , validated  from  u
ser_constraints  where  table_name = 'AA';

CONSTRAINT_  C  TABLE  STATUS    DEFERRABLE      DEFERRED   VALIDATED
----------- - ----- -------- -------------- --------- -------------
PK          P  AA     ENABLED   NOT  DEFERRABLE  IMMEDIATE  VALIDATED

--可以看到，默认的是NOT DEFERRABLE，下面我们将它转为immediate试试

SQL >  set  constraint  pk  immediate;
set  constraint  pk  immediate
*
ERROR  at  line  1 :
ORA - 02447 :  cannot  defer  a  constraint  that  is  not  deferrable

--增加一个uk，指定deferrable initially immidiate|deferred

SQL >  alter  table  aa  add  constraint  uk  unique ( name)  deferrable  initially  immediate;

Table  altered .

SQL >  select  CONSTRAINT_NAME  , CONSTRAINT_TYPE , TABLE_NAME , STATUS , DEFERRABLE , DEFERRED , validated  from  u
ser_constraints  where  table_name = 'AA';

CONSTRAINT_  C  TABLE  STATUS    DEFERRABLE      DEFERRED   VALIDATED
----------- - ----- -------- -------------- --------- -------------
PK          P  AA     ENABLED   NOT  DEFERRABLE  IMMEDIATE  VALIDATED
UK           U  AA     ENABLED   DEFERRABLE      IMMEDIATE  VALIDATED

SQL >  select  *  from  aa;

no  rows  selected

SQL >  insert  into  aa  values( 1 , 'SDF');

1  row  created .

SQL >  insert  into  aa  values( 2 , 'SDF');
insert  into  aa  values( 2 , 'SDF')
*
ERROR  at  line  1 :
ORA - 00001 :  unique  constraint ( LYN . UK)  violated


SQL >  set  constraints  uk  deferred;

Constraint  set .

SQL >  select  *  from  aa;

no  rows  selected

SQL >  insert  into  aa  values( 1 , 'SDF');

1  row  created .

SQL >  insert  into  aa  values( 2 , 'SDF');

1  row  created .

SQL >  commit;
commit
*
ERROR  at  line  1 :
ORA - 02091 :  transaction  rolled  back
ORA - 00001 :  unique  constraint ( LYN . UK)  violated

--此外，可以在SESSION里设定所有约束是立即检查(immediate)还是延迟检查(deferred)，当然，这只影响初始时指定可延迟的(deferrable)约束。

SQL >  alter  session  set  constraint = immediate;

Session  altered .

SQL >  alter  session  set  constraints = deferred;

Session  altered .

--4种状态对比-----------------
SQL >  drop  table  aa  purge;

Table  dropped .

SQL >  create  table  aa( id  number  primary  key);

Table  created .

--enable validate(默认)

SQL >   select  CONSTRAINT_NAME  , CONSTRAINT_TYPE , TABLE_NAME , STATUS , DEFERRABLE , DEFERRED , validated  from
user_constraints  where  table_name = 'AA';

CONSTRAINT_  C  TABLE  STATUS    DEFERRABLE      DEFERRED   VALIDATED
----------- - ----- -------- -------------- --------- -------------
SYS_C002829 P  AA     ENABLED   NOT  DEFERRABLE  IMMEDIATE  VALIDATED

SQL >  insert  into  aa  select  2010  from  dual;

1  row  created .

SQL >  insert  into  aa  select  2010  from  dual;
insert  into  aa  select  2010  from  dual
*
ERROR  at  line  1 :
ORA - 00001 :  unique  constraint ( LYN . SYS_C002829)  violated

--disable novalidate

SQL >  alter  table  aa  modify  constraints  SYS_C002829  disable;

Table  altered .

SQL >   select  CONSTRAINT_NAME  , CONSTRAINT_TYPE , TABLE_NAME , STATUS , DEFERRABLE , DEFERRED , validated  from
user_constraints  where  table_name = 'AA';

CONSTRAINT_  C  TABLE  STATUS    DEFERRABLE      DEFERRED   VALIDATED
----------- - ----- -------- -------------- --------- -------------
SYS_C002829 P  AA     DISABLED  NOT  DEFERRABLE  IMMEDIATE  NOT  VALIDATED

SQL >  select  *  from  aa;

         ID
----------
       2010

SQL >  insert  into  aa  select  2010  from  dual;

1  row  created .

SQL >  insert  into  aa  select  2010  from  dual;

1  row  created .

SQL >  commit;

Commit  complete .

--disable validate

SQL >  alter  table  aa  modify  constraints  SYS_C002829  disable  validate;
alter  table  aa  modify  constraints  SYS_C002829  disable  validate
*
ERROR  at  line  1 :
ORA - 02437 :  cannot  validate ( LYN . SYS_C002829)  -  primary  key  violated


SQL >  select  *  from  aa;

         ID
----------
       2010
       2010
       2010

SQL >  delete  aa  where  rownum < 3;

2  rows  deleted .

SQL >  select  rownum , id  from  aa;

     ROWNUM          ID
---------- ----------
          1        2010

SQL >  alter  table  aa  modify  constraints  SYS_C002829  disable  validate;

Table  altered .

SQL >  update  aa  set  id = 2012;
update  aa  set  id = 2012
*
ERROR  at  line  1 :
ORA - 25128 :  No  insert / update / delete  on  table  with  constraint ( LYN . SYS_C002829)  disabled and  validated


SQL >  insert  into  aa  select  2012  from  dual;
insert  into  aa  select  2012  from  dual
*
ERROR  at  line  1 :
ORA - 25128 :  No  insert / update / delete  on  table  with  constraint ( LYN . SYS_C002829)  disabled and  validated


SQL >  delete  from  aa;
delete  from  aa
*
ERROR  at  line  1 :
ORA - 25128 :  No  insert / update / delete  on  table  with  constraint ( LYN . SYS_C002829)  disabled and  validated


SQL >  truncate  table  aa;
truncate  table  aa
*
ERROR  at  line  1 :
ORA - 25128 :  No  insert / update / delete  on  table  with  constraint ( LYN . SYS_C002829)  disabled and  validated


--enable novalidate


SQL >  alter  table  aa  enable  novalidate  constraint  SYS_C002829;

Table  altered .

SQL >  insert  into  aa  select  2010  from  dual;
insert  into  aa  select  2010  from  dual
*
ERROR  at  line  1 :
ORA - 00001 :  unique  constraint ( LYN . SYS_C002829)  violated

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21584437/viewspace-715985/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21584437/viewspace-715985/