Foreign key constraint difference noted when there is no difference

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:
Program version:
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 currently have 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.


Best Answer

  • Eddie DEddie D Posts: 1,700 Rose Gold 5
    Accepted Answer
    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 Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]


Sign In or Register to comment.