Re: Thoughts on how to avoid a massive integer update.

[Rob Sargent <robjsargent@xxxxxxxxx>]

On May 8, 2020, at 2:43 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Fri, May 8, 2020 at 1:41 PM Rob Sargent <robjsargent@xxxxxxxxx> wrote:

My understanding is the keys in the info_table need to change.  That causes the very expensive update in the update in the data tables. No? 

The keys in the info_table need to change because their contents are no longer legal to be stored (OP has not specified but think using an integer value of someones social security number as a key).  The FK side of the relationship equality has the same illegal data values problem and need to be changed too.

David J.

Wow, I couldn’t disagree more ;)

Let’s say it were an ssn.  A side from the fact that those are mutable and should not be used as an id (not to say identifier), the ssn should only be in the table aligned with that_person.  Other tables refering to that person should use the locally assigned and arbitrary identifier for that person.  (Frankly there is no “natural key” for a person.)

The scheme I propose has a chance of completing relatively unobtrusively and quickly allowing a migration over time if deemed necessary.  Likely to finished just in time for the next interuption.