当前位置:首页 > 开发 > 数据库 > 正文

ORACLE中CONSTRAINT的四对属性

发表于: 2012-11-13   作者:daizj   来源:转载   浏览:
摘要: ORACLE中CONSTRAINT的四对属性 summary:在data migrate时,某些表的约束总是困扰着我们,让我们的migratet举步维艰,如何利用约束本身的属性来处理这些问题呢?本文详细介绍了约束的四对属性: Deferrable/not deferrable, Deferred/immediate, enalbe/disable, validate/novalidate,以及如
ORACLE中CONSTRAINT的四对属性
summary:在data migrate时,某些表的约束总是困扰着我们,让我们的migratet举步维艰,如何利用约束本身的属性来处理这些问题呢?本文详细介绍了约束的四对属性: Deferrable/not deferrable, Deferred/immediate, enalbe/disable, validate/novalidate,以及如何应用这些属性灵活应对相关问题


是否要求满足约束         Validate                                Novalidate
------------------------------------------------------------------------------------
                        已有记录     新增/修改记录        已有记录    新增/修改记录
Enable                  Yes        Yes                     No          Yes
Disable                 Yes         No                      No           No


Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate;

Validate和Novalidate对Enable和Disable没有任何默认暗示。
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。


1.     Deferrable,not deferrable(default value)

1)      这对属性是定义是否可以defer,defer是指作检查的时机,如果在commit的时check为Defer,否则为immediate .只有在设置Deferrable才可以设置另一个属性2-- Deferred,immediate.

2)      设置defer check的方法有两种(前提是建立了Deferrable的contraint)

a.       通过建contraint时指定Deferred值

b.      通过会话级别的语句修改

           SET CONSTRAINT(s) contraint_name/all  immediate/deferred.

3)      这对属性是在创建的constraint的时候定义的,不能被修改.

4)  notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index  

2.     Deferred,immediate(default value) 

1)      这对属性定义是否defer.   Deferred: check on commit; immediate: check immediate.

2)      If constraint is not deferrable,immediate is only choice.

3)      For  example:



CREATE TABLE games

  (scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)

   INITIALLY DEFERRED DEFERRABLE);

insert into games values(1);

insert into games values(1);

commit;--在此报错

You will not get a error util you commit it;





SET CONSTRAINT(s) unq_num immediate;--修改属性

insert into games values(2);

insert into games values(2);--在此报错

commit;

You will get a error when you execute the second sql;

3.      novalidate, validate(default value)

1)      这对属性定义constraint是否对表中已经存在的数据作检查,例如:

create table t(id number);
insert   into t values(1);
insert   into t values(2);
alter table t add constraint ch_100 check(id>=100);  --失败
alter table t add constraint ch_100 check(id>=100) novalidate;--成功

2)      notice:与唯一索引相关的contraint(例如pk,uk),要做到以上的效果还必须设置为Deferrable(只是建立非唯一性索引),因为在维护索引是,如果违反了唯一性也会报错,所以必须建立非唯一性索引.例如:

drop table t;
create table t(id number);
insert   into t values(1);
insert   into t values(1);
alter table t add constraint ch_100 unique(id) ; --报错
alter table t add constraint ch_100  unique(id) novalidate; --报错
alter table t add constraint ch_100  unique(id)  deferrable  novalidate;--成功

4.     disable, enalbe(default value)     

1)      启用和禁用constraint.在新建pk和uk时定义了disable,将不建立相应的索引.

ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;

ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;

alter table t add constraint ch_100  unique(id) disable;



2)      DISABLE  uk或pk作了些什么:

Disable非deferrable 的pk、uk,将删除相应的索引(除非指定了keep index,但是keep下来的索引是唯一性的,insert数据时还是要作唯一性检查的),在enable时重建索引.

Disbale  deferrable 的pk、uk将保留原来的索引(因为原来的索引就是非唯一性的,不影响insert的操作).



3)      一些操作经验

KEEP INDEX要注意的:

  a.ALTER TABLE games  DISAble  CONSTRAINT fk_num keep index;--唯一索引被保留,所以还是不能插入重复的数据.不应该keep index.

  b.ALTER TABLE games  DISAble  CONSTRAINT fk_num;--如果上一步被执行,那么此语句什么都不做,唯一索引仍被保留,此时应该先enable在disable.如果原来的状态是able的话,那么唯一索引将被删除.





5.    报告约束异常



    如果校验约束时存在异常,则返回一个错误,且完整性约束仍保持未被校验状态。当约束存在异常时,一个语句就不能正确执行,则此语句被回滚。如果存在异常,必须要更新或删除了约束的所有异常后,才可以校验约束。但是在使用ALTER TABLE语句不能确定哪一行违反约束,为了确定哪一行,在发布ENABLE子句中带有EXCEPTION选项的ALTER TABLE语句。



    EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中。在启用约束前,硬创建一个合适的异常报告表,用来接收ENABLE子句的EXCEPTION选项信息,可以直接执行'?\rdbms\admin\utlexcpt.sql'或'?\rdbms\admin\utlexcpt1.sql'脚本来进行创建。注意:这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型。



    使用的语法如下:



    ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

或者

    alter table team disable validate   constraint id_num   EXCEPTIONS INTO EXCEPTIONS;--这个方法不创建索引
 

    如果存在异常,则直接查询 SELECT * FROM EXCEPTIONS; 即可。如果需要更加详细的信息,则可以使用ROWID与原表的ROWID进行关联,这样就可以查出原始表中当前行的所有信息,并进行修改。

如何利用EXCEPTIONS来处理实践中的一些问题:

(1)在已有的constraint上加载数据

        先 disable

        再加载

        再enable and EXCEPTIONS INTO EXCEPTIONS

     再根据EXCEPTIONS表的纪录delete

      最后enable

(2)新建constraint,原有的数据违反了这个约束

        先create disable

        再enable and EXCEPTIONS INTO EXCEPTIONS
      以下是一个新建外键的例子  

alter table team add constraint id_num foreign key(id)  references games(scores) INITIALLY disable; 
   alter table team enable   constraint id_num   EXCEPTIONS INTO EXCEPTIONS;           select * from EXCEPTIONS;









6.     关于contraint操作的一些脚步

定义约束


         alter table t add constraint ch_100  unique(id) disable  ;

   alter table t add constraint ch_100  unique(id);
        alter table t add constraint ch_100  unique(id) deferred deferrable novalidate;

CREATE TABLE dept_20

   (employee_id     NUMBER(4), 

    commission_pct  NUMBER(7,2),

    department_id,

   CONSTRAINT fk_deptno

      FOREIGN  KEY (department_id)

      REFERENCES  departments(department_id)

      on delete/set null cascade);



ALTER TABLE dept_20

   ADD CONSTRAINT fk_empid_hiredate

   FOREIGN KEY (employee_id, hire_date)

   REFERENCES hr.job_history(employee_id, start_date)

   EXCEPTIONS INTO EXCEPTIONS;



CREATE TABLE divisions

   (div_no    NUMBER  CONSTRAINT check_divno

              CHECK (div_no BETWEEN 10 AND 99)

              DISABLE,

    div_name  VARCHAR2(9)  CONSTRAINT check_divname

              CHECK (div_name = UPPER(div_name))

              DISABLE,

    office    VARCHAR2(10)  CONSTRAINT check_office

              CHECK (office IN ('DALLAS','BOSTON',

              'PARIS','TOKYO'))

              DISABLE);







修改或删除现有的约束



    注:在使用外键参考了PRIMARY或UNIQUE键时,不能停用或删除被参考约束

 

    延迟校验的启用停用:

    SET CONSTRAINT(s) unq_num/all immediate;

      SET CONSTRAINT(s) unq_num/all deferred;



    停用:

    ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,

                     DISABLE UNIQUE (dname, loc) KEEP INDEX;



    启用非校验:

    ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;

    ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,

                     ENABLE NOVALIDATE UNIQUE (dname, loc);



    启用校验:

    ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;

    ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;



    若要停用/删除相关的FOREIGN KEY约束,则:

    ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;



    删除:

    ALTER TABLE dept DROP UNIQUE (dname, loc);

    ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,

                    DROP CONSTRAINT dept_fkey;

ORACLE中CONSTRAINT的四对属性

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
//用alert修改约束除了not null外,改变其它的约束都要加constraint关键字,比如 alert table table_1
//用alert修改约束除了not null外,改变其它的约束都要加constraint关键字,比如 alert table table_1
自己画了一块FMC-VHDCI四层板,外接VmodCAM,接口定义如下 #CAMA PIN CONSTRACT NET "CAMA_D_I[7]"
概要 物料是整个ERP的根基,几乎所有的业务操作都是围着物料转的。而Oracle EBS中的物料属性有近280
TextView 属性效果展示: 效果描述: 第一个文本框后面加了一个图片,第二个中间省略,字母全大写
varStatus是<c:forEach>jstl循环标签的一个属性,varStatus属性。就拿varStatus=“status”来
Android开发中,大部分控件都有visibility这个属性,其属性有3个分别为“visible ”、“invisible”
LinearLayout中的layout_weight属性 布局LinearLayout代码 <?xml version="1.0" encoding="utf-8
LinearLayout中的layout_weight属性 布局LinearLayout代码 Xml代码 <?xmlversion="1.0"encoding=
CSS中的Position属性 转载:http://www.cnblogs.com/rainman/archive/2009/04/12/1433988.html 也许
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号