agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@lists.postgresql.org
Subject: Best way to change values of a primary key referenced by many tables
Date: Wed, 21 Oct 2020 22:27:29 +0200 (CEST)
Message-ID: <VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena> (raw)
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
view thread (3+ messages) latest in thread
Message-ID: <VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena>
Permalink: ../VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena
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: 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: <VisenaEmail.45.50d53c6557c272d6.1754cb2dafc@tc7-visena>
* 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