[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)
[每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)正确答案:D根据题意如下操作:一、创建表dept[html]gyj@OCM> CREATE TABLE DEPT2 (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 EMP2 (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) ENABLE13 );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的数据及约束。[html]gyj@OCM> SELECT deptno,dname FROM dept;DEPTNO DNAME---------- --------------10 IT20 HRgyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp;EMPNO ENAME DEPTNO---------- ---------- ----------1 KING 102 HARI 20gyj@OCM> col CONSTRAINT_NAME for a10gyj@OCM> col R_CONSTRAINT_NAME for a10gyj@OCM> col TABLE_NAME for a10gyj@OCM> col INDEX_NAME for a10gyj@OCM> select CONSTRAINT_NAME,R_CONSTRAINT_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT_TYPE,STATUS2 from user_constraints where table_name3 in('EMP','DEPT');CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS---------- ---------- ---------- ---------- - --------PK_DEPT DEPT PK_DEPT P ENABLEDPK_EMP EMP PK_EMP P ENABLEDFK_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:40BIN$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,STATUS2 from user_constraints where table_name3 in('EMP','DEPT');CONSTRAINT R_CONSTRAI TABLE_NAME INDEX_NAME C STATUS---------- ---------- ---------- ---------- - --------PK_DEPT DEPT PK_DEPT P ENABLED八、闪回表emp[html]gyj@OCM> flashback table emp to before drop;Flashback complete.九、查回收站,已没信息,说明表和唯一索引(主键约束)都被闪回了,但外键没有被闪回(失去了外键的制约),如下操作。[html]gyj@OCM> select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE ,DROPTIME from recyclebin;no rows selectedgyj@OCM> SELECT EMPNO,ENAME,DEPTNO FROM emp;EMPNO ENAME DEPTNO---------- ---------- ----------1 KING 102 HARI 20gyj@OCM> col index_name for a50gyj@OCM> select index_name from user_indexes where table_name='EMP';INDEX_NAME--------------------------------------------------BIN$5LNox53pT0PgQ4rZqMD+/Q==$0gyj@OCM> col CONSTRAINT_NAME for a40上一个:Oracle多行记录合并自定义函数
下一个:x$bh详解
- 更多Flash疑问解答:
- 今天教大家学会用U5制作成翻页相册
- Flash MX 2004 ActionScript图文教程(九)
- Flash MX 2004 ActionScript图文教程(八)
- Flash MX 2004 ActionScript图文教程(七)
- Flash MX 视频导入功能详解
- Flash5 画任意直线教程
- FLASH -- 关于变量的问题FLASH -- 关于变量的问题
- Flash本地加密
- flash场景之间相互跳转的实现方法
- Flash那样动感十足制作PPT按钮跟我来
- Flash Mx使用技巧十二则
- 使用Flash mx制作旋转的时钟效果
- PowerPoint中插入Flas易做图的方法
- 用FlashMX制作拖动悬浮窗口
- 在Flash中巧妙替换字体