Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Andreas Joseph Krogh
Date:
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 REFERENCES my_user (id),    name      VARCHAR NOT NULL
);

CREATE TABLE my_project
(    entity_id  BIGINT PRIMARY KEY,    name       VARCHAR NOT NULL,    created_by BIGINT  NOT NULL REFERENCES my_user (id)
);

CREATE TABLE my_company
(    entity_id  BIGINT PRIMARY KEY,    name       VARCHAR NOT NULL,    created_by BIGINT  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

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

From
"David G. Johnston"
Date:
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.

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

From
Iuri Sampaio
Date:
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_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.

Attachment