CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Xandrmoro
Xandrmoro

Reputation: 93

Is there a way to remap all foreign key references for a given ID to another ID in Postgres?

Lets say, there is a table with a simple numeric ID column with values 1 and 2. And there are few dozens of other tables, that have rows with ID = 1 referenced in their foreign keys. Is there any way to cascade update all those references to 2 without generating updates for all the referencing tables?

UPDATE to clarify it a bit further.

We got a dictionary table like that, called CompanyTypes:

ID | Name
---------
1  | Bar
2  | Restaurant

And we have a bunch of tables referencing that dictionary in their FKs. For simplicity, lets go with one named Companies:

ID | Name       | Type
----------------------
1  | Cool bar   | 1
2  | Not a bar  | 2

And now there came an administrative decision that we no longer need bars as a distinct company type, and now all the bars have to be remapped to be restaurants without altering the db schema (so I can not add mapping table or something like that). Yes, I can write a query to generate queries to update each table individually (UPDATE Companies SET Type = 2 WHERE Type = 1 and so on), but is there any way to trick the db engine into doing it for me?

Upvotes: 2

Views: 828

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

Try an anonymous code block to iterate over all tables that have a foreign key related to CompanyTypes and concatenate the table name with an UPDATE statement:

DO $$
DECLARE
  row record;
BEGIN
    FOR row IN SELECT DISTINCT fk_tco.table_name as fk_table_name
               FROM information_schema.referential_constraints rco
               JOIN information_schema.table_constraints fk_tco ON
                  rco.constraint_name = fk_tco.constraint_name AND
                  rco.constraint_schema = fk_tco.table_schema
               JOIN information_schema.table_constraints pk_tco ON
                  rco.unique_constraint_name = pk_tco.constraint_name AND
                  rco.unique_constraint_schema = pk_tco.table_schema
               WHERE pk_tco.table_name = 'companytypes' 
               ORDER BY fk_table_name 
    LOOP
      EXECUTE format('UPDATE %s SET Type = 2 WHERE Type = 1',row.fk_table_name);
    END LOOP;
END;
$$;

Demo: db<>fiddle

Upvotes: 2

Related Questions