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

 

192.

You execute this command.

SQL> DROP TABLE scott.item CASCADE CONSTRAINTS;

The ITEM table has a primary key that is referenced by a foreign key in the ORDERS table RECYCLEBIN IS enabled.

Which three statements are true ?

A) The ITEM table is moved to the RECYCLEBIN.

B) The ITEM table is dropped.

C) The foreign constraint on the ORDERS table is dropped.

D) The foreign constraint on the ORDERS table is disabled.

E) The ORDERS table is dropped.

F) The ORDERS table is moved to the RECYCLEBIN.

G) The foreign constraint on the ORDERS table is moved to the RECYCLEBIN.

解析：

--主表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 p values (1,'a');

insert into p values (2,'b');

commit;

 

insert into c values (100,1,1);

insert into c values (200,2,1);

commit;

 

 

则当删除p表时，如不特殊说明，则 drop table p 系统会出现如下错误警告的信息而不会允许执行。

 

SQL> drop table p;

drop table p

           *

第 1 行出现错误:

ORA-02449: 表中的唯一/主键被外键引用

 

解决办法,使用语句可以刪除从表（或称子表）的constraint，从而可实现主表（或称父表）的drop;

 

SQL> Drop table p cascade constraints;

 

表已删除。

 

 

SQL> Flashback table p to before drop;

闪回完成

 

insert into c values (300,3,1);

SQL> commit;

 

SQL> select * from p;

 

       PID PNAME

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

         1 a

         2 b

 

SQL> select * from c;

 

       DID        PID        CNT

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

       100          1          1

       200          2          1

       300          3          1

 

SQL> insert into p values (1,'c');

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (TEST.BIN$YFRv1nd6QU+BOVRlpS8pKw==$0)

SQL> insert into c values (200,2,1);

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (TEST.SYS_C0011128)

 

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

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

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