转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/10286737
![[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)......98_OCP1z0](http://img.blog.csdn.net/20130824231307656?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ3VveUpvZQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
![[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)......98_OCP1z0_02](http://img.blog.csdn.net/20130824231334125?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ3VveUpvZQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
正确答案：D
根据题意如下操作：
一、创建表dept
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
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插入数据
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
gyj@OCM> drop table emp; Table dropped.
六、查回收站，可以看出表和索引同时被删除除
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的外键也被删除了
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 八、闪回表emp
gyj@OCM> flashback table emp to before drop; Flashback complete.
九、查回收站，已没信息，说明表和唯一索引（主键约束）都被闪回了，但外键没有被闪回（失去了外键的制约），如下操作。
gyj@OCM> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin; no rows selected gyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp; EMPNO ENAME DEPTNO ---------- ---------- ---------- 1 KING 10 2 HARI 20 gyj@OCM> col index_name for a50 gyj@OCM> select index_name from user_indexes where table_name='EMP'; INDEX_NAME -------------------------------------------------- BIN$5LNox53pT0PgQ4rZqMD+/Q==$0 gyj@OCM> col CONSTRAINT_NAME for a40 gyj@OCM> col index_name for a40 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_NAME R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS ---------------------------------------- ---------- ---------- ---------------------------------------- - -------- PK_DEPT DEPT PK_DEPT P ENABLED BIN$5LNox53oT0PgQ4rZqMD+/Q==$0 EMP BIN$5LNox53pT0PgQ4rZqMD+/Q==$0 P ENABLED 十、插入两条数据，第一次插入报错(emp中已存在empno为2的员工了，empno是主键)，第二次插入成功。
gyj@OCM> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10); INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10) * ERROR at line 1: ORA-00001: unique constraint (GYJ.BIN$5LNox53oT0PgQ4rZqMD+/Q==$0) violated gyj@OCM> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(3,'ING',55); 1 row created. gyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp; EMPNO ENAME DEPTNO ---------- ---------- ---------- 1 KING 10 2 HARI 20 3 ING 55
十一、闪回索引和约束的名称还是：BIN$5LNox53pT0PgQ4rZqMD+/Q==$0和BIN$5LNox53oT0PgQ4rZqMD+/Q==$0，最好修改索引和约束，如下操作：
gyj@OCM> ALTER INDEX "BIN$5LNox53pT0PgQ4rZqMD+/Q==$0" RENAME TO PK_EMP; Index altered. gyj@OCM> ALTER TABLE EMP RENAME CONSTRAINT "BIN$5LNox53oT0PgQ4rZqMD+/Q==$0" TO PK_EMP; Table altered. 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_NAME R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS ---------------------------------------- ---------- ---------- ---------------------------------------- - -------- PK_DEPT DEPT PK_DEPT P ENABLED PK_EMP EMP PK_EMP P ENABLED gyj@OCM> select index_name from user_indexes where table_name='EMP'; INDEX_NAME ---------------------------------------- PK_EMP 呵呵，这样插入记录时报错就能看到正常的约束名称了。
gyj@OCM> INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10); INSERT INTO emp(EMPNO,ENAME,DEPTNO) values(2,'COTT',10) * ERROR at line 1: ORA-00001: unique constraint (GYJ.PK_EMP) violated
总结：flashback table闪回表的同时也闪回了索引（此索引是建主键约束时产生的唯一索引，即也闪回了主键约束），但没有闪回外键约束。
QQ：252803295
学习交流QQ群：
DSI&Core Search Ⅰ 群：127149411（技术:已满）
DSI&Core Search Ⅱ 群：177089463（技术:未满）
DSI&Core Search Ⅲ 群：284596437（技术:未满）
DSI&Core Search Ⅳ 群：192136702（技术:未满）
DSI&Core Search Ⅴ 群：285030382（闲聊:未满）
MAIL：oracledba_cn@hotmail.com
BLOG： http://blog.csdn.net/guoyjoe
WEIBO：http://weibo.com/guoyJoe0218
ITPUB： http://www.itpub.net/space-uid-28460966.html
OCM： http://education.oracle.com/education/otn/YGuo.HTM
