This discussion is archived
2 Replies Latest reply: Jun 18, 2012 8:04 AM by PuraVidaOTN RSS

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

PuraVidaOTN Newbie
Currently Being Moderated
-- (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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points