Support for Surrogate Key and New Column Value Customization
mbruegel
Posts: 43 Bronze 2
Often times tables have surrogate key (sequence generated) values, that are use in in addition to a set of natural (alternate / unique) key constraints.
The surrogate key value typically has no inherent meaning or value (i.e. I don't care about syncing them across instances).
Some common use cases may be:
- I want to sync my core data columns, but I do not want to sync my surrogate sequence values across instances (e.g. to avoid ID conflicts)
- I want to be able to identify / reference a sequence (from the target instance) to be used as the "new" value for the column in the target instance
- I want to be able to identify columns whose values do not to sync (e.g. set to NULL, set to a default, etc.)
Does Data Compare provide direct support for any of the above?
The surrogate key value typically has no inherent meaning or value (i.e. I don't care about syncing them across instances).
Some common use cases may be:
- I want to sync my core data columns, but I do not want to sync my surrogate sequence values across instances (e.g. to avoid ID conflicts)
- I want to be able to identify / reference a sequence (from the target instance) to be used as the "new" value for the column in the target instance
- I want to be able to identify columns whose values do not to sync (e.g. set to NULL, set to a default, etc.)
Does Data Compare provide direct support for any of the above?
Tagged:
Answers
If your target table is set to automatically insert the sequence value that you want, then you would just need to not include that column in the comparison.
So if you have a sequence and table like (Oracle 12C2):
You would un-map col1 when setting up the comparison in the "Tables & Views" tab by un-checking the column in the "Columns in Comparison" section (I realize this isn't the table described above, but imagine NAME is col1):
The comparison would match entries based on the id column and then create an insert statement to add or update and col2 and col3 based on that value and then the col1 would auto generate.
If the id column (or the column you have set as the comparison key) is the one that has the sequence value, then this would not work, but will not create duplicate ID's as if the comparison key matches, it will update the row in the target, but any in the source not in the target would be created with the ID from the source and these would possibly be ID's that the sequence on the target may yet come to.
Your second use-case is not possible and would be a feature request. Please go to the Oracle tools uservoice and create a suggestion for this. Currently the tool is only for synchronizing data so it will make the target look like the source.
I'm not sure what you mean by your last use-case - in SQL Data Compare it will attempt to synchronize all columns that are shown in the image above (e.g. ID and NAME).
The SEQUENCES themselves are compared using Schema Compare for Oracle - normally the current value is also compared, but there is an option to ignore the current value so this may also be of use to you when working with the sequences.
Kind regards,
Alex
Have you visited our Help Center?