Re: Best way to change values of a primary key referenced by many tables - Mailing list pgsql-sql
Andreas,
if there’s a chance to redesign your datamodel from scratch, I’d go benchmarking one of the best Framework I’ve ever seen, for PostgreSQL object datamodel designing. OpenACS / Prject-Open
I’d recreate the datamodel following the references available here
For object design:
This datamodel is based on OpenACS, a robust and high scalable community based system
Best wishes,
I
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_idUpdating 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.
