169.You have two tables with referential integrity enforced between them. You need to insert data to the
child table first because it is going to be a long transaction and data for the parent table will be available in
a later stage, which can be inserted as part of the same transaction.
View the Exhibit to examine the commands used to create tables.
Which action would you take to delay the referential integrity checking until the end of the transaction?
A.Set the constraint to deferred before starting the transaction.
B.Alter the constraint to NOVALIDATE state before starting the transaction.
C.Enable the resumable mode for the session before starting the transaction.
D.Set the COMMIT_WAIT parameter to FORCE_WAIT for the session before starting the transaction.
答案:A
解析:这道题是说,两张表存在外键约束,现在想要先插入子表在插入父表,怎么处理
这里主要是考察initially immediate和initially deferred
第一种为马上检查,第二种为延迟检查如果给表中添加行,那么只有在commit的时候才会对该约束进行检查,
延迟约束必须在创建的时候指出,不能创建好后再修改为延迟约束
我们测试一下
SQL> create table items(item_code number(4) constraint pk primary key deferrable initially immediate);
Table created.
SQL> create table orders(item_code number(4),constraint fk_item foreign key(item_code) references items(item_code) on delete cascade deferrable initially immediate);
Table created.
--直接插入子表失败
SQL> insert into orders values(10);
insert into orders values(10)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.FK_ITEM) violated - parent key not found
--临时修改为延迟约束
SQL> set constraints fk_item deferred;
Constraint set.
SQL> insert into orders values(10);
1 row created.
SQL> insert into items values(10);
1 row created.
SQL> commit;
Commit complete.
SQL>
child table first because it is going to be a long transaction and data for the parent table will be available in
a later stage, which can be inserted as part of the same transaction.
View the Exhibit to examine the commands used to create tables.
Which action would you take to delay the referential integrity checking until the end of the transaction?
A.Set the constraint to deferred before starting the transaction.
B.Alter the constraint to NOVALIDATE state before starting the transaction.
C.Enable the resumable mode for the session before starting the transaction.
D.Set the COMMIT_WAIT parameter to FORCE_WAIT for the session before starting the transaction.
答案:A
解析:这道题是说,两张表存在外键约束,现在想要先插入子表在插入父表,怎么处理
这里主要是考察initially immediate和initially deferred
第一种为马上检查,第二种为延迟检查如果给表中添加行,那么只有在commit的时候才会对该约束进行检查,
延迟约束必须在创建的时候指出,不能创建好后再修改为延迟约束
我们测试一下
SQL> create table items(item_code number(4) constraint pk primary key deferrable initially immediate);
Table created.
SQL> create table orders(item_code number(4),constraint fk_item foreign key(item_code) references items(item_code) on delete cascade deferrable initially immediate);
Table created.
--直接插入子表失败
SQL> insert into orders values(10);
insert into orders values(10)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.FK_ITEM) violated - parent key not found
--临时修改为延迟约束
SQL> set constraints fk_item deferred;
Constraint set.
SQL> insert into orders values(10);
1 row created.
SQL> insert into items values(10);
1 row created.
SQL> commit;
Commit complete.
SQL>