Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kVKhp-0001XH-JX for pgsql-sql@arkaria.postgresql.org; Wed, 21 Oct 2020 20:27:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kVKho-0004s6-BG for pgsql-sql@arkaria.postgresql.org; Wed, 21 Oct 2020 20:27:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kVKho-0004oA-0c for pgsql-sql@lists.postgresql.org; Wed, 21 Oct 2020 20:27:32 +0000 Received: from post.visena.com ([46.226.10.50]) by magus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_128_CBC_SHA1:128) (Exim 4.92) (envelope-from ) id 1kVKhm-0001dl-5G for pgsql-sql@lists.postgresql.org; Wed, 21 Oct 2020 20:27:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:Message-ID:To:From:Date; bh=U4EGSlbFqr4eYxeXnHu5nxpHXTJ0YOS854BZ1W+OlAg=; b=X7O9Hu7uXYW7W6nubzPcZYSpUnC8l4QR4QfGycK/bAb9CzbcU8JXyt5/icXTykCaCG71sbq/FJyyCffbDRbGsYTRrJ5eacIhOdiBVcXHDGSFwKsSiMa1IcbVxi2pcNtsy23k346j1nzyrjnbxnsjHyuRb1hzxF6mQt93VeMhc3E=; Received: from tc7-visena.wh.internal.visena.com ([10.0.1.10]) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1kVKhl-0005Jd-Fs for pgsql-sql@lists.postgresql.org; Wed, 21 Oct 2020 22:27:29 +0200 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.90_1) (envelope-from ) id 1kVKhl-0006aA-8b for pgsql-sql@lists.postgresql.org; Wed, 21 Oct 2020 22:27:29 +0200 Date: Wed, 21 Oct 2020 22:27:29 +0200 (CEST) From: Andreas Joseph Krogh To: pgsql-sql@lists.postgresql.org Message-ID: Subject: Best way to change values of a primary key referenced by many tables MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_305_218772939.1603312049113" X-Mailer: Visena Mail 2.2.436-SNAPSHOT List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_305_218772939.1603312049113 Content-Type: multipart/related; boundary="----=_Part_306_1593687001.1603312049113" ------=_Part_306_1593687001.1603312049113 Content-Type: multipart/alternative; boundary="----=_Part_307_983416419.1603312049129" ------=_Part_307_983416419.1603312049129 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 ------=_Part_307_983416419.1603312049129 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi.
=C2=A0
I'm looking for the easiest way to change the=C2=A0vaules=C2= =A0of=C2=A0a=C2=A0PK=C2=A0of a table (my_user), which is referenced by many= FKs, with the minimum effort.
=C2=A0
Here's an example-schema:
=C2=A0
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
);
=C2=A0
Now - I want to refactor so that=C2=A0my_user.id=C2=A0has the same value = as my_person.entity_id
=C2=A0
Updating the value of=C2=A0my_use= r.id=C2=A0sounds 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 h= aving an FK to=C2=A0my_user.id?
=C2=A0
Not that some FKs are=C2=A0DEFERRABLE, others have "ON DELETE&quo= t;, and the requirement is to not mess with that.
=C2=A0
So - I'm basically looking for (I think) a way to add "ON UPDATE = CASCADE" to all columns referencing=C2=A0it, update the values and the= n removing all "ON UPDATE CASCADE" on the referencing columns.
=C2=A0
Appreciate suggestions, thanks.
=C2=A0
">
--
Andrea= s Joseph Krogh
------=_Part_307_983416419.1603312049129-- ------=_Part_306_1593687001.1603312049113-- ------=_Part_305_218772939.1603312049113--