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.