pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Best way to change values of a primary key referenced by many tables 3+ messages / 3 participants [nested] [flat]
* Best way to change values of a primary key referenced by many tables @ 2020-10-21 20:27 Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 1 reply; 3+ messages in thread From: Andreas Joseph Krogh @ 2020-10-21 20:27 UTC (permalink / raw) To: pgsql-sql@lists.postgresql.org Hi. I'm looking for the easiest way to change the vaules of a PK of a table (my_user), which is referenced by many FKs, with the minimum effort. Here's an example-schema: CREATE TABLE my_user ( id BIGSERIAL PRIMARY KEY, username VARCHAR NOT NULL UNIQUE); CREATE TABLE my_person ( entity_id BIGSERIAL PRIMARY KEY, user_id BIGINT REFERENCESmy_user (id), name VARCHAR NOT NULL ); CREATE TABLE my_project (entity_id BIGINT PRIMARY KEY, name VARCHAR NOT NULL, created_by BIGINT NOT NULL REFERENCESmy_user (id) ); CREATE TABLE my_company ( entity_id BIGINT PRIMARY KEY, name VARCHAR NOT NULL, created_byBIGINT NOT NULL REFERENCES my_user (id) DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE my_product ( entity_id BIGINT PRIMARY KEY, name VARCHAR NOT NULL ,created_by BIGINT NOT NULL REFERENCES my_user (id) ON DELETE CASCADE ); 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? 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. Appreciate suggestions, thanks. -- Andreas Joseph Krogh ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to change values of a primary key referenced by many tables @ 2020-10-21 21:25 David G. Johnston <david.g.johnston@gmail.com> parent: Andreas Joseph Krogh <andreas@visena.com> 0 siblings, 1 reply; 3+ messages in thread From: David G. Johnston @ 2020-10-21 21:25 UTC (permalink / raw) To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org> 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to change values of a primary key referenced by many tables @ 2020-10-21 22:11 Iuri Sampaio <iuri.sampaio@gmail.com> parent: David G. Johnston <david.g.johnston@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Iuri Sampaio @ 2020-10-21 22:11 UTC (permalink / raw) To: David G. Johnston <david.g.johnston@gmail.com>; Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql <pgsql-sql@lists.postgresql.org> 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:^ permalink raw reply [nested|flat] 3+ messages in threadpo-object-type-hierarchy.781.gif (image/gif, 25.3K) download
view image
end of thread, other threads:[~2020-10-21 22:11 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-10-21 20:27 Best way to change values of a primary key referenced by many tables Andreas Joseph Krogh <andreas@visena.com> 2020-10-21 21:25 ` David G. Johnston <david.g.johnston@gmail.com> 2020-10-21 22:11 ` Iuri Sampaio <iuri.sampaio@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox