1

Asume I have schema like this:

CREATE TABLE "user"
(
  id BIGSERIAL PRIMARY KEY,
  sid VARCHAR(32) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  login VARCHAR(32) NOT NULL,
);

CREATE TABLE user_authority_relation
(
    id BIGSERIAL PRIMARY KEY,
    user_id int8 NOT NULL,
    authority_id int8 NOT NULL,
    CONSTRAINT user_fk FOREIGN KEY (user_id)
    REFERENCES "user" (id) ON DELETE CASCADE,
    CONSTRAINT authority_fk FOREIGN KEY (authority_id)
    REFERENCES "authority" (id) ON DELETE CASCADE,
    UNIQUE (user_id, authority_id)
);

CREATE TABLE note (
  id BIGSERIAL PRIMARY KEY,
  content text NOT NULL,
  sender_id int8 NOT NULL,
  sent_date timestamp without time zone NOT NULL,
  important BOOLEAN NOT NULL,
  type VARCHAR(16) NOT NULL,
  CONSTRAINT user_fk FOREIGN KEY (sender_id)
  REFERENCES "user" (id) ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
);

Now, I must delete user X from db, but instead of delete all related objects I want replace id to my new user Y. So for example if some note.sender_id is X.id i want set it too Y.id, same for user_authority_relation.user_id I want change from X.id to Y.id.

Is it possible in sql/postgresql?

EDIT:

I have written too broadly. I know I can write update like:

update user_authority_relation 
set user_id = y.id

but what I want achive is one update on all objects that refers to my X by constraint. I my db I have over a dozen tables that refer to user. I don't want to write update for all of them separately but one update.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Mr Jedi
  • 33,658
  • 8
  • 30
  • 40
  • update user_authority_relation set user_id = y; update note set sender_id = y; delete from user where id = x;? –  Apr 29 '15 at 16:35
  • @MerelyUseful I know I can do update for every table ;) I edited question, know is more specific. I want do one update on all tables that refers to "user".id – Mr Jedi Apr 30 '15 at 06:08
  • See http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys for how to get a list of all the foreign keys, you could read this into an array and loop through to generate dynamic sql that updated each of the tables. I think this is Not a Good Idea though. It's probably clearer just to write the code to do the updates individually. I'd certainly curse the developer who left something like that behind for me to figure out. –  Apr 30 '15 at 08:17

0 Answers0