Options
Schema Compare and Cross Schema Foreign Key constraints
Brian.Lieb
Posts: 29 New member
I have two schemas, and recently I added a foreign key constraint in one schema to a table's primary key in another schema.
For example:
Table 1:
create table schema1.standard_price (
"ID" number(6) not null,
"product_id" number(6) not null,
constraint sp_id_pk PRIMARY KEY ("ID"),
constraint sp_p_fk FOREIGN KEY (id) references schema2.product("ID")
);
Table 2:
create table schema2.product (
"ID" number(6) not null,
"SKU" varchar2(25) not null,
constraint p_id_pk PRIMARY KEY ("ID")
);
Anyway, since the sp_p_fk was added later, I did a schema compare between the development database and the production database using the schema1 as the compare schema and those tables show no differences.
However, when I choose to compare development and production databases using BOTH schemas, that change does appear.
Is this as designed? I would hope that the standard price table, though referencing a table in another schema would indicate that there is a difference between it and the production table.
For example:
Table 1:
create table schema1.standard_price (
"ID" number(6) not null,
"product_id" number(6) not null,
constraint sp_id_pk PRIMARY KEY ("ID"),
constraint sp_p_fk FOREIGN KEY (id) references schema2.product("ID")
);
Table 2:
create table schema2.product (
"ID" number(6) not null,
"SKU" varchar2(25) not null,
constraint p_id_pk PRIMARY KEY ("ID")
);
Anyway, since the sp_p_fk was added later, I did a schema compare between the development database and the production database using the schema1 as the compare schema and those tables show no differences.
However, when I choose to compare development and production databases using BOTH schemas, that change does appear.
Is this as designed? I would hope that the standard price table, though referencing a table in another schema would indicate that there is a difference between it and the production table.
Comments
Thank you for your forum post and sorry for the delay in replying to you.
The behaviour you have described does not appear to be correct to me. Therefore a support ticket has been created for you. Would please send an email to support@red-gate.com and enter 'Support Call #50014' into the subject field of the message. In the email, please include the following:
1. Version of Schema Compare you are using? 2. Version of Oracle used for both the development and production databases 3. Enable the Verbose logging of Schema Compare for Oracle as per this
HELP ARTICLE.4. Perform a comparison of schema 1 between the development database and the production database. Screen shot the results view for the comparison for the 'standard_price' table. Locate the log file and SCO_Comparison.log file (See the help article in 3) and attach copies of both log files to your email.
5. Perform the comparison of both schemas between the development database and the production database. Screen shot the results view for the comparison for the 'standard_price' and the 'product' tables. Again, locate the log file and SCO_Comparison.log file from this second comparison and attach copies of both log files to your email.
Hopefully the information in the log files, in particular the SCO_Comparison.log files will help identify the cause of you issue.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com