Oracle SQL Developer是Oracle近几年来推出的一款免费的图形界面SQL PL/SQL开发工具,在11g中它甚至被集成到了Oracle server软件中;如果你平常有用这款IDE的话,大概会注意到它提供了十分实用的Database copy,diff,export功能。
当在Oracle SQL developer中使用sys用户登录数据库,并尝试导出某些schema的ddl定义时,可能会丢失一些constraint约束的ddl信息,如以下例子:
我们尝试导出scott用户的表,索引以及约束的相关ddl,当使用sys用户登录数据库时,Oracle SQL developer给出的SQL:
-------------------------------------------------------- -- File created - Monday-August-30-2010 -------------------------------------------------------- -------------------------------------------------------- -- DDL for Table BONUS -------------------------------------------------------- CREATE TABLE "BONUS" ( "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER, "COMM" NUMBER ) ; -------------------------------------------------------- -- DDL for Table DEPT -------------------------------------------------------- CREATE TABLE "DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) ; -------------------------------------------------------- -- DDL for Table EMP -------------------------------------------------------- CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) ; -------------------------------------------------------- -- DDL for Table SALGRADE -------------------------------------------------------- CREATE TABLE "SALGRADE" ( "GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER ) ; -------------------------------------------------------- -- DDL for Index PK_DEPT -------------------------------------------------------- CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO") ; -------------------------------------------------------- -- DDL for Index PK_EMP -------------------------------------------------------- CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO") ;
以上SQL中未给出PK和FK约束的ddl信息。
如果使用scott用户登录数据库,导出自身schema的table,index,constraint定义信息时:
-------------------------------------------------------- -- File created - Monday-August-30-2010 -------------------------------------------------------- -------------------------------------------------------- -- DDL for Table BONUS -------------------------------------------------------- CREATE TABLE "BONUS" ( "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER, "COMM" NUMBER ) ; -------------------------------------------------------- -- DDL for Table DEPT -------------------------------------------------------- CREATE TABLE "DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) ; -------------------------------------------------------- -- DDL for Table EMP -------------------------------------------------------- CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) ; -------------------------------------------------------- -- DDL for Table SALGRADE -------------------------------------------------------- CREATE TABLE "SALGRADE" ( "GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER ) ; -------------------------------------------------------- -- Constraints for Table EMP -------------------------------------------------------- ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE; -------------------------------------------------------- -- Constraints for Table DEPT -------------------------------------------------------- ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") ENABLE; -------------------------------------------------------- -- DDL for Index PK_EMP -------------------------------------------------------- CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO") ; -------------------------------------------------------- -- DDL for Index PK_DEPT -------------------------------------------------------- CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO") ; -------------------------------------------------------- -- Ref Constraints for Table EMP -------------------------------------------------------- ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE;
Oracle SQL developer给出了正确的SQL,包含FK和PK约束。
这个Bug在8月(2010 Aug)被确认将影响此前所有版本的Oracle SQL developer。
Leave a Reply