2 Replies Latest reply: Jun 18, 2012 10:04 AM by PuraVidaOTN RSS

    reproducible issues with database diff: FK, index, dup index, no constraint

    PuraVidaOTN
      -- (1) Run the following SQL to create two main objects:
      -- schema_old.CUST and
      -- schema_new.CUST

      -- OLD

      drop user schema_old cascade;
      create user schema_old identified by xxx;
      grant dba to schema_old;

      connect schema_old/xxx

      create table PRD_ID_PARENT ( "ID" NUMBER, PRIMARY KEY ("ID") ENABLE );
      create table TP_ID_PARENT( "ID" NUMBER, PRIMARY KEY ("ID") ENABLE );


      CREATE TABLE "CUST"
      ( "ID" NUMBER(19,0) NOT NULL ENABLE,
      "CRD" NUMBER(19,0) NOT NULL ENABLE,
      "PRD_ID" NUMBER(19,0),
      "TP_ID" NUMBER(19,0) NOT NULL ENABLE,
      --
      CHECK (CRD>=-1) ENABLE,
      --
      PRIMARY KEY ("ID") ENABLE,
      UNIQUE ("PRD_ID","TP_ID") ENABLE,
      --
      CONSTRAINT "FK_PRD_ID" FOREIGN KEY ("PRD_ID")
      REFERENCES "PRD_ID_PARENT" ("ID") ENABLE,
      CONSTRAINT "FK_TP_ID" FOREIGN KEY ("TP_ID")
      REFERENCES "TP_ID_PARENT" ("ID") ENABLE
      );

      -- NEW

      drop user schema_new cascade;
      create user schema_new identified by xxx;
      grant dba to schema_new;

      connect schema_new/xxx

      create table PRD_ID_PARENT ( "ID" NUMBER, PRIMARY KEY ("ID") ENABLE );
      create table TP_ID_PARENT( "ID" NUMBER, PRIMARY KEY ("ID") ENABLE );


      CREATE TABLE "CUST"
      ( "ID" NUMBER(19,0) NOT NULL ENABLE,
      "CRD" NUMBER(19,0) NOT NULL ENABLE,
      "PRD_ID" NUMBER(19,0),
      "TP_ID" NUMBER(19,0) NOT NULL ENABLE,
      --
      constraint CK_CRD CHECK (CRD>=-1) ENABLE,
      --
      constraint PK_ID PRIMARY KEY ("ID") ENABLE,
      constraint UK_PRD_ID_TP_ID UNIQUE ("PRD_ID","TP_ID") ENABLE,
      --
      CONSTRAINT "FK_PRD_ID" FOREIGN KEY ("PRD_ID")
      REFERENCES "PRD_ID_PARENT" ("ID") ENABLE,
      CONSTRAINT "FK_TP_ID" FOREIGN KEY ("TP_ID")
      REFERENCES "TP_ID_PARENT" ("ID") ENABLE
      );

      -- (2) Use SQL Developer DATABASE diff to compare them with these options:
      -- under DDL generation options, unselect "schema".
      -- under DDL comparison options: select "match constraints by definition"

      -- I have the following FOUR issues as labelled by (A), (B), (C), (D).

      -- (A) It shows the difference scripts:

      -- ALTER TABLE "CUST" DROP CONSTRAINT "FK_PRD_ID";
      -- ALTER TABLE "CUST" DROP CONSTRAINT "FK_TP_ID";
      -- ALTER TABLE "CUST" ADD CONSTRAINT "FK_PRD_ID" FOREIGN KEY ("PRD_ID") REFERENCES "SCHEMA_OLD"."PRD_ID_PARENT"("ID") ENABLE;
      -- ALTER TABLE "CUST" ADD CONSTRAINT "FK_TP_ID" FOREIGN KEY ("TP_ID") REFERENCES "SCHEMA_OLD"."TP_ID_PARENT"("ID") ENABLE;

      -- It should not be shown as there is NO difference between the Foreign keys in these two tables
      -- under schema_old and schema_new.

      -- The "reason" shows the difference is that the schema names are different. Please note the schema name
      -- "SCHEMA_OLD" appears in the last two "ALTER TABLE" statements.

      -- But since I have chosen to ignore the schema under DDL generation options, it should ignore
      -- the schema as I asked. Therefore this is a bug. If this bug is not fixed, it will greatly
      -- limits the applicability of this tool, as you can not effectively compare schemas under
      -- the same database.

      -- When I move the object to another database and make the the schema names the same, the "difference"
      -- went away.

      -- (B) It also shows there are two NEW indexes:

      -- PK_ID
      -- UK_PRD_ID_TP_ID

      -- These are NOT new indexes by index DEFINITION. It is just the names are different.

      -- Even I can make the CONSTRAINTS' name the same, but the underlying INDEXES may have
      -- different names. For example, the primary key is PK_ID, but the index may be SYS_1234.

      -- As I have chosen under DDL comparison options: select "match constraints by definition",
      -- and the constraint and index are very closely related, I expect it will MATCH INDEXES
      -- BY DEFINATION, but not by names. But obviously it is not the case. Therefore,
      -- We should have a option to match INDEXES either by NAME, or by DEFINITION.

      -- (C) Under the schema browser, the SQL is shown as follows. And this is incorrect in the sense
      -- that the indexes has been created by the first SQL statement. On the other hand, the
      -- "Quick DDL" uses ALTER TABLE and is correct. We should consolidate "Quick DDL" with the
      -- "SQL" under schema browser, and make it correct.

      -- CREATE TABLE "SCHEMA_OLD"."CUST"
      -- (
      -- "ID" NUMBER(19,0) NOT NULL ENABLE,
      -- "CRD" NUMBER(19,0) NOT NULL ENABLE,
      -- "PRD_ID" NUMBER(19,0),
      -- "TP_ID" NUMBER(19,0) NOT NULL ENABLE,
      -- CHECK (CRD>=-1) ENABLE,
      -- PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ENABLE,
      -- UNIQUE ("PRD_ID", "TP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ENABLE,
      -- CONSTRAINT "FK_PRD_ID" FOREIGN KEY ("PRD_ID") REFERENCES "SCHEMA_OLD"."PRD_ID_PARENT" ("ID") ENABLE,
      -- CONSTRAINT "FK_TP_ID" FOREIGN KEY ("TP_ID") REFERENCES "SCHEMA_OLD"."TP_ID_PARENT" ("ID") ENABLE
      -- )
      -- SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ;
      --
      -- CREATE UNIQUE INDEX "SCHEMA_OLD"."SYS_C0046852" ON "SCHEMA_OLD"."CUST"
      -- (
      -- "ID"
      -- )
      -- PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ;
      --
      -- CREATE UNIQUE INDEX "SCHEMA_OLD"."SYS_C0046853" ON "SCHEMA_OLD"."CUST"
      -- (
      -- "PRD_ID", "TP_ID"
      -- )
      -- PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ;

      -- (D) The schema browser and the tree under connections list TABLES, VIEWS, INDEXES, etc but
      -- "CONSTRAINTS". WHY? Isn't a constraint a seperate object?

      -- My support account does not have "SQL developer" listed. How do I open a case with Oracle regarding the
      -- issues?