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

On Wed, Oct 21, 2020 at 1:27 PM Andreas Joseph Krogh <andreas(at)visena(dot)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.