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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Andreas Joseph Krogh-2
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
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Wed, Oct 21, 2020 at 1:27 PM Andreas Joseph Krogh <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

Iuri Sampaio
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 <[hidden email]> wrote:

On Wed, Oct 21, 2020 at 1:27 PM Andreas Joseph Krogh <[hidden email]> 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.