20090906_042_关于Constraint考题

 

042-考题：

5. A constraint in a table is defined with the INITIALLY IMMEDIATE clause. You executed the ALTER TABLE

command with the ENABLE VALIDATE option to enable the constraint that was disabled. What are the two

effects of this command? (Choose two.)

A) It fails if any existing row violates the constraint.

B) It does not validate the existing data in the table.

C) It enables the constraint to be enforced at the end of each transaction.

D) It prevents insert, update, and delete operations on the table while the constraint is in the process of being enabled.

答案:A,D

 

 

 

 

处理方法：

 

disable/enable validate/novalidate 的区别

启用约束:
  enable( validate)   :启用约束,创建索引,对已有及新加入的数据执行约束.
  enable novalidate  :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.

禁用约束:
  disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
  disable validate      :关闭约束,删除索引,不能对表进行插入/更新/删除等操作.

参考：http://www.host01.com/article/database/00060004/0542413042257005.htm

            http://www.itpub.net/thread-800518-1-1.html

            http://sns.linuxpk.com/space-52196-do-blog-id-16032.html

            http://www.itpub.net/thread-4902-1-1.html

 

 

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

请问constraint中：not deferrable与 deferrable initially immediate 有何区别？谢谢

 

        创建constraint时有这样一个选项： 

 

        not deferrable | deferrable [ initially { immediate | deferred } ] 

 

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

        not deferrable与 deferrable initially immediate 

 

        岂不都是check at the end of every DML statement， 

 

        请问两种状态有何区别？谢谢各位前辈。

 

 

not deferrable 与initially immediate是一样的,与deferrable initially immediate则不同。后者允许将constraint再改为initially deferred, 前者不允许更改:

    drop table test

    create table test (f1 number(3) primary key)

    insert into test (f1) select 1 from dual;

    insert into test (f1) select 2 from dual;

 

    create table test2 (f1 number(3) constraint fk references test(f1) initially immediate);

 

       insert into test2 (f1) select 1 from dual;

 

    alter table test2 modify constraint fk initially deferred;

    alter table test2 modify constraint fk initially deferred

    *

    ERROR 位于第 1 行:

    ORA-02447: 无法延迟不可延迟的约束条件

 

 

 

     create table test3 (f1 number(3) constraint fk1 references test(f1) deferrable initially immediate);

       insert into test3 (f1) select 3 from dual;

 

     alter table test3 modify constraint fk1 initially deferred;

       insert into test3 (f1) select 3 from dual;

 

           当提交时，调试以下错误：

                 1.ora-02091:事务处理已回退。     

                 1.ora-02291: 违反完整约束条件(ZZH_TEST.FK1)-未找到父项关键字  

 

     alter table test3 modify constraint fk1 initially immediate;     

           修改回来后，那么一插入就报错.