2018年oracle认证OCP考试052新题解析-188

View the Exhibit and examine the commands used to create two tables. You wish to insert data into the child table before parent table. Which two actions would avoid errors when inserting a row into the child table? 
A) Disable the primary key constraint before starting the transaction. 
B) Disable the foreign key constraint before starting the transaction. 
C) Set the foreign key constraint to deferred before starting the transaction.
D) Set the primary key constraint to deferred before starting the transaction. 
E) Set the foreign key to immediate and non deferrable.

Answer:BC

相关试验：

先建的表为父表P
create table p(pid number primary key,pname varchar2(10)); 
后建的需引用或（遗传）父表的为子表C
create table c(did number primary key,pid number ,cnt number, constraint cpk FOREIGN KEY (pid) REFERENCES p(pid));

--由于有外建约束，所以下面的数据无法插入
insert into c values (100,3,1);
ORA-02291: 违反完整约束条件 (SYS.CPK) - 未找到父项关键字

解决方法1.禁用约束
alter table c
disable constraint cpk;

insert into c values (100,3,1);
插入成功

但出来混迟早要还，如果要启用约束
alter table c
enable constraint cpk;

则需验证已存在的数据

启用约束时可以设置是否验证现有数据，在enable后添加关键字novalidate则不验证现有数据，validate则验证现有数据（默认，省略即为验证）。如：

alter table c
enable novalidate constraint cpk;

解决方法1.约束延迟

删除普通外键约束,建立延迟外键约束
alter table c drop constraint cpk;
alter table c add constraint cpk foreign key (pid) references p(pid) deferrable initially immediate;

insert into c values (100,3,1);
ORA-02291: 违反完整约束条件 (SYS.CPK) - 未找到父项关键字
--将延迟验证设置为立即验证：则在插入时出错
alter table c drop constraint cpk;
alter table c add constraint cpk foreign key (pid) references p(pid) deferrable initially immediate;
--将延迟验证设置为延迟验证, 当事务提交时或调用set constraint[s] immediate语句时才验证.
alter table c drop constraint cpk;
alter table c add constraint cpk foreign key (pid) references p(pid) deferrable initially deferred;
SQL> insert into c values (100,3,1);

已创建 1 行。

SQL> commit;
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-02291: 违反完整约束条件 (SYS.CPK) - 未找到父项关键字

约束延迟指在事务提交时再验证，默认为不延迟。

开启约束延迟：set constraint 约束名 deferred;
set constraint cpk deferred;
关闭约束延迟：set constraint 约束名 immediate;
set constraint cpk immediate;

 

更多2019年oracle认证OCP考试071新题解析讨论请加入QQ群：479384490

更多2019年oracle认证OCP考试052新题解析讨论请加入QQ群：479384490

更多2019年oracle认证OCP考试053新题解析讨论请加入QQ群：479384490