Re: Best way to change values of a primary key referenced by many tables

On Rab. I 5, 1442 AH, at 18:25, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Wed, Oct 21, 2020 at 1:27 PM Andreas Joseph Krogh <andreas@visena.com> wrote:

Now - I want to refactor so that my_user.id has the same value as my_person.entity_id

 

Updating the value of my_user.id sounds simple, but how do I do that, and update all other tables pointing to it with this new value, with as little effort as possible, ie. don't have to ALTER/UPDATE every table having an FK to my_user.id?

Given those constraints I'd suggest that your problem has no solution.

 

Not that some FKs are DEFERRABLE, others have "ON DELETE", and the requirement is to not mess with that.

 

So - I'm basically looking for (I think) a way to add "ON UPDATE CASCADE" to all columns referencing it, update the values and then removing all "ON UPDATE CASCADE" on the referencing columns.

While ALTER TABLE can alter a FK constraint it only can change the deferrability property, not the trigger properties.

 

Appreciate suggestions, thanks.

Say no.

David J.