[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY )
正确答案：D
根据题意如下操作：
一、创建表dept
[
html]
gyj@OCM> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2,0),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13),
5 CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
6 );
Table created.
二、创建表emp
[html]
gyj@OCM> CREATE TABLE EMP
2 (EMPNO NUMBER(4,0),
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4,0),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2,0),
10 CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
11 CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
12 REFERENCES DEPT (DEPTNO) ENABLE
13 );
Table created.
三、分别向表dept和表emp插入数据
[html]
gyj@OCM> insert into dept values(10,'IT',null);
1 row created.
gyj@OCM> insert into dept values(20,'HR',null);
1 row created.
gyj@OCM> insert into dept(DEPTNO,DNAME) values(10,'IT');
1 row created.
gyj@OCM> insert into dept(DEPTNO,DNAME) values(20,'HR');
1 row created.
gyj@OCM> insert into emp(EMPNO,ENAME,DEPTNO) values(1,'KING',10);
1 row created.
gyj@OCM> insert into emp(EMPNO,ENAME,DEPTNO) values(2,'HARI',20);
1 row created.
gyj@OCM> COMMIT;
Commit complete.
四、查dept和emp的数据及约束。
gyj@OCM> SELECT deptno,dname FROM dept;
DEPTNO DNAME
---------- --------------
10 IT
20 HR
gyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
1 KING 10
2 HARI 20
gyj@OCM> col CONSTRAINT_NAME for a10
gyj@OCM> col R_CONSTRAINT_NAME for a10
gyj@OCM> col TABLE_NAME for a10
gyj@OCM> col INDEX_NAME for a10
gyj@OCM> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------- ---------- ---------- ---------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED
PK_EMP EMP PK_EMP P ENABLED
FK_DEPTNO PK_DEPT EMP R ENABLED
五、删除表emp
[html]
gyj@OCM> drop table emp;
Table dropped.
六、查回收站，可以看出表和索引同时被删除除
[html]
gyj@OCM> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_N OPERATION TYPE DROPTIME
------------------------------ ---------- --------- ---------- -------------------
BIN$5LNox53pT0PgQ4rZqMD+/Q==$0 PK_EMP DROP INDEX 2013-08-24:22:33:40
BIN$5LNox53qT0PgQ4rZqMD+/Q==$0 EMP DROP TABLE 2013-08-24:22:33:40
七、并且可以查出表emp的外键也被删除了
[html]
gyj@OCM> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS
2 from user_constraints where table_name
3 in('EMP','DEPT');
CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS
---------- ---------- ---------- ---------- - --------
PK_DEPT DEPT PK_DEPT P ENABLED