this is a third part of our discussion on how to merge a database table with an auton number primary key used by Foreign keys in the first part we've got familiar with ksdb merge tools and its ID remap merge feature that can be used to solve this task in the second part we took a look at more complex examples of using this feature and now let's talk a bit more about ID remap merge no new examples just some notes regarding mapping tables approach and limit ations of suggested solution I would rec
ommend watching the first two parts if you haven't done this yet let's talk a bit about some benefits of the suggested solution and mapping tables at first this solution is producing a script that does not rely on any concrete ID values as a result the same script can be used for different environments for example we could apply this script to a QA or production copy database verify that it does what we we need and then run the same script on the real production environment next as we saw in par
t two the merge can be divided into steps for example we can make script number one for the reference data like lookup tables then we could make a script number two for the operational data and apply it using mapping tables from the script number one mapping tables can also be used for a number of other tasks at first to identify that some row is a result of our merge for example after merge we can observe some Bugs see that some entities have some strange Behavior or missing some details we can
use mapping tables to identify that the issue is related only to our merge result and nothing else next after merge we may have a need to compare original Source roles with their copies in the Target database for example to identify any changes that could happen in the source after completion of merge this can be done using map mapping tables mapping tables can also be used to roll back merge results without need to make the full restore of the database backup this can be useful if the database
is actively used and with restore we may lose some data which is not related to our merge at all now let's talk about limitations of ID remap merge at first it works only for single column auton number primary Keys used as a single column foreign key if we use auton number primary Keys then it is the most popular database design model but in some cases such a database may have some exclusions from that rule some tables may have hard-coded IDs which are not auton numbers it should not be a big i
ssue because if it was hard-coded somehow in the source then probably we can decide how to hardcode it in the Target next some child tables May reference an unique constraint in the parent table instead of primary key or in some database management system systems the Auto number column and the primary key column can be not the same both of these cases are not a best practice but sometimes that happens and for the next limitation let's take another example based on our database model table order
log to order item has foreign keys to auton number primary keys and as we saw these foreign key values were updated properly with ID remap merge now let's imagine that the composite primary key of order log to order item is used as a foreign key for some other table values of such composite keys are based on auton numbers but since it is not a single column Primary in foreign Keys the current implementation of ID remap merge will not work for this properly this table just will not be shown in ID
remap configuration and no insert statements will be generated for it but if you're performing a one-time data migration there is a simple workaround you can create additional temporary single column foreign Keys use it for ID remap and then drop them after merge completion let's get back to the list of limitations the next limitation is that ID remap merge cannot be applied for tables without dependency level dependency level is missing for tables participating in cycled foreign key references
let's imagine that table user has foreign key photo ID and table photo has foreign key author user ID referencing the user we've got a foreign key cycle if we try to do ID remap merge with such a data we can get something similar to deadlock if we insert user row it may reference to photo ID which is not inserted yet and if we try to fix this by inserting that photo ID first then it also may fail because its author user ID is not inserted yet such a logical cycle needs to be broken somehow we n
eed to either disable foreign Keys during merge or to perform initial merge without these IDs at least for one of these tables and then perform update when all IDs are in place and one more limitation is that if we merge multiple tables at once then they must have the same dependency level part two of this video explains why this limitation was made if you are faced with any of these limitations or something else does not work for you feel free to contact our support we will try to help you and
now just to few words about future plans all the things described here are just an initial implementation of ID remap merge as we can see it has some limitations but it can be very useful for many typical use cases and we are not going to stop on it we have a lot of plans on how it can be made better you probably noticed in our demos that some dialogues have too many words explaining what happens and that's probably not needed for someone who already understands how it works so one of our tasks
is to simplify ID remap merge UI the next task is to overcome some limitations that we have discussed also during our tests we have noticed that it would be good to have more configuration for some steps of ID remap merge and the last for today kstb merge tools is not young but it is still actively evolving we have other growing features like diff profiles or command line scripting and it may happen that in the future ID remap merge will be integrated with some of these features in order to prov
ide you more automation less unnecessary steps and better user experience thanks for watching
Comments