Wholesale search and replace fkey relationships of an integer ID to a UUID?

Question

I built a table with a primary key that is an incremeneting integer, since then it has been added as a foreign key to various other tables.

Is there a single query or something along those lines that I can run which will update the integer to a UUID type and update all the foreign key relationships along with it?

The only other option I can think of doing this safely is to add a new uuid column to the table, autofill values, make it the primary key, then painfully go through every table with an fkey relationship to it and update it to point at the UUID and figure out the equivalent SQL query to do this.

Answer 1

It's not possible to do in a single query.

A. Detect all foreign key constraints

That's doable in a single query. You can look for recipes how to list foreign keys, I like this: https://stackoverflow.com/a/40728972/1168212

Take a look at https://www.db-fiddle.com/f/qwRzRSFaJx4KDMYn2GEXTe/3

B. Generate ALTER TABLE statements

Or that could be DROP-CREATE, the way you choose for db refactoring depends on your db.

C. Refresh hasura metadata

In the best case you will just click "Refresh" and everything will be fine.

It's very probable that you will need to manually redefine relationships.