Foreign key constraint difference noted when there is no difference
larry.talley@noaa.gov
Posts: 2 Bronze 1
We use Schema Compare for Oracle extensively and depend on it for development/deployment processes.
Today when comparing schema AKFISH with scripts we get 22 objects that exist in both but are different. Three of these objects truly are different. However, 19 have no differences detectable via other difference tools. That is, when we tell Schema Compare for Oracle to update our scripts (via the deployment wizard) and then we use other difference tools to compare the script folder with our git repository, we find 3 files with differences, not 22. And in fact when in the Schema Compare user interface, if we highlight an object from the list of "objects that exist in both but are different" and then look at the pane at the bottom to see the actual differences, the "Next" button is dithered, because there are no differences. We can look at the object's script line-by-line and see for ourselves that the objects are the same (although this is tedious!)
Here is an extract from the SCO_Comparison.log:
---
I currently have 5.7.0.2630 installed, my boss may have a slightly older version, but we both get the same false difference. This appears to be new behavior this week. Our DBA may possibly have made changes in some aspect of the Oracle configuration, we have not been able to rule that out.
Today when comparing schema AKFISH with scripts we get 22 objects that exist in both but are different. Three of these objects truly are different. However, 19 have no differences detectable via other difference tools. That is, when we tell Schema Compare for Oracle to update our scripts (via the deployment wizard) and then we use other difference tools to compare the script folder with our git repository, we find 3 files with differences, not 22. And in fact when in the Schema Compare user interface, if we highlight an object from the list of "objects that exist in both but are different" and then look at the pane at the bottom to see the actual differences, the "Next" button is dithered, because there are no differences. We can look at the object's script line-by-line and see for ourselves that the objects are the same (although this is tedious!)
Here is an extract from the SCO_Comparison.log:
---
Program version: 5.7.0.2630
Options: IncludeScriptHeader, IgnoreWhitespace, IgnoreDoubleQuotes, FastDependencies, IgnoreCrossSchemaDependencies, IgnoreSequenceCurrentValue, IncludeSetDefineOff, IgnoreMviewStartWithValue, DetectRenamedColumns, IncludeCrossSchemaPermissions
Time: Wed, 11 Mar 2020 14:33:04 GMT
type DatabaseComparison: Different
Tables , type TableDifference , number of differences 17 , total number of objects 474
type TableDifference, akfish.cdq_del_rpt_specie <-> akfish.cdq_del_rpt_specie: Different
PrimaryKeyDifference, RefConstraints , type RefConstraintDifference , number of differences 0 , total number of objects 0
SupplementalLogGroups , type LogGroupDifference , number of differences 0 , total number of objects 0
ExternalInformation, IlmPolicies , type IlmPolicyDifference , number of differences 0 , total number of objects 0
PeriodFors , type PeriodForDifference , number of differences 0 , total number of objects 0
Clustering, NestedTables , type NestedTableDifference , number of differences 0 , total number of objects 0
Storage, Columns , type ColumnDifference , number of differences 0 , total number of objects 16
IndexConstraints , type IndexConstraintDifference , number of differences 0 , total number of objects 1
ForeignKeyConstraints , type ForeignKeyConstraintDifference , number of differences 1 , total number of objects 1
type ForeignKeyConstraintDifference: Different
ColumnsDifferent: True
CheckConstraints , type CheckConstraintDifference , number of differences 0 , total number of objects 3
---
I note that the difference it found was a ForeignKeyConstraintDifference. There are two FK constraints on this table. Neither has any differences. I do note that all of the 19 false differences happen to include a column that has the name YEAR and in Schema Compare for Oracle it has inserted double quotes, i.e., "YEAR". I think perhaps that only FK constraints with the column named YEAR are going to generate this false difference.
---
I note that the difference it found was a ForeignKeyConstraintDifference. There are two FK constraints on this table. Neither has any differences. I do note that all of the 19 false differences happen to include a column that has the name YEAR and in Schema Compare for Oracle it has inserted double quotes, i.e., "YEAR". I think perhaps that only FK constraints with the column named YEAR are going to generate this false difference.
I currently have 5.7.0.2630 installed, my boss may have a slightly older version, but we both get the same false difference. This appears to be new behavior this week. Our DBA may possibly have made changes in some aspect of the Oracle configuration, we have not been able to rule that out.
Tagged:
Best Answer
-
Eddie D
Posts: 1,803 Rose Gold 5
Hi, thank you for your reply.
It does appear from your screen shot that no differences are highlighted in the SQL Differences view. As a supported customer, a ticket has been created for you within our call ticketing system as I need to request additional information from you that you may not wish to post in a public forum.
The results as per your screen shot, is essentially in two parts. The upper portion which is the results of the comparison confirms if the object is identical, or existing in both but different or exists only in the source or target.
The lower part is from a different comparison process to simply show the textual differences.
Please lookout for my message and if one does not appear in your Inbox, please check your SPAM folder.
Many Thanks
Eddie
Answers
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com