ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 约束(constraint)的几个参数的小研究

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

原创 Linux操作系统 作者:逍遥三人 时间:2012-02-09 20:57:01 0 删除 编辑
转载: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 (namedeferrable 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.UKviolated


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.UKviolated

--此外,可以在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_C002829violated

--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_C002829disabledand 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_C002829disabledand validated


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


SQL> truncate table aa;
truncate table aa
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (LYN.SYS_C002829disabledand 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_C002829violated

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

下一篇: Oracle 索引 详解
请登录后发表评论 登录
全部评论

注册时间:2009-05-07

  • 博文量
    73
  • 访问量
    110140