agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Iuri Sampaio <iuri.sampaio@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Best way to change values of a primary key referenced by many tables
Date: Wed, 21 Oct 2020 19:11:57 -0300
Message-ID: <83795111-25CB-470F-B9AF-3271123EF282@gmail.com> (raw)
In-Reply-To: <CAKFQuwZqxqswJrsHzCcM4GKKfAn6+Ft2-Q0C=NYo4d==j39UhQ@mail.gmail.com>
References: <VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena>
<CAKFQuwZqxqswJrsHzCcM4GKKfAn6+Ft2-Q0C=NYo4d==j39UhQ@mail.gmail.com>
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
http://www.project-open.com/en/list-data-model <http://www.project-open.com/en/list-data-model;
I’d recreate the datamodel following the references available here
http://www.project-open.com/en/list-data-model <http://www.project-open.com/en/list-data-model;
For object design:
https://openacs.org/doc/object-system-design <https://openacs.org/doc/object-system-design;
This datamodel is based on OpenACS, a robust and high scalable community based system
https://openacs.org <https://openacs.org/;
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 <mailto: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.
Attachments:
[image/gif] po-object-type-hierarchy.781.gif (25.3K, ../83795111-25CB-470F-B9AF-3271123EF282@gmail.com/3-po-object-type-hierarchy.781.gif)
download | view image
view thread (3+ messages)
Message-ID: <83795111-25CB-470F-B9AF-3271123EF282@gmail.com>
Permalink: ../83795111-25CB-470F-B9AF-3271123EF282@gmail.com/
Also on: postgresql.org/message-id/83795111-25CB-470F-B9AF-3271123EF282@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iuri.sampaio@gmail.com, david.g.johnston@gmail.com, andreas@visena.com, pgsql-sql@lists.postgresql.org
Subject: Re: Best way to change values of a primary key referenced by many tables
In-Reply-To: <83795111-25CB-470F-B9AF-3271123EF282@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox