Chinaunix首页 | 论坛 | 博客
  • 博客访问: 625278
  • 博文数量: 72
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 435
  • 用 户 组: 普通用户
  • 注册时间: 2013-04-27 20:07
个人简介

郭一军,网名guoyJoe,尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com

文章分类
文章存档

2014年(72)

分类: Oracle

2014-06-19 10:36:37

转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/10286737



正确答案: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



阅读(245) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册