PostgreSQL - properly change ID of table row

Question

How to change id of some table's row?

Like:

UPDATE table SET id=10 WHERE id=5;

But in way that it would cascade changes to every other table that references this table with that id?

I want to do this, because I need to import data from another database which has most of the same tables, but ids are different. So if ids would match old database, it would be easier to import data correctly.

Answer 1

Suppose you have these two tables:

create table referenced (id integer primary key);
create table referencer (a integer references referenced (id));

Table referencer references table referenced:

=> \d referencer
  Table "public.referencer"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
Foreign-key constraints:
    "referencer_a_fkey" FOREIGN KEY (a) REFERENCES referenced(id)

Then you insert a value in both:

insert into referenced values (1);
insert into referencer values (1);

select *
from
    referenced rd
    inner join
    referencer rr on rd.id = rr.a
;
 id | a 
----+---
  1 | 1

Now you want to change the reference to on update cascade:

alter table referencer
    drop constraint referencer_a_fkey,
    add foreign key (a) references referenced (id) on update cascade;

And update it:

update referenced set id = 2;

select *
from
    referenced rd
    inner join
    referencer rr on rd.id = rr.a
;
 id | a 
----+---
  2 | 2

Now you will have another problem in the referenced table primary key if the updated id already exists. But that would make another question.

UPDATE

This is dangerous so backup the db first. It must be done as superuser:

update pg_constraint
set confupdtype = 'c'
where conname in (
    select
        c.conname
    from
        pg_constraint c
        inner join
        pg_class referenced on referenced.oid = c.confrelid
    where
        referenced.relname = 'referenced'
        and
        c.contype = 'f'
);

It will change all the foreign key constraints on the referenced table to on update cascade

Answer 2

You will need to change your foreign key and set ON UPDATE action to CASCADE. When you change a value, all associated values will be changed too.

This is an example how to define it:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON UPDATE CASCADE,
    order_id integer REFERENCES orders ON UPDATE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

For more information see http://www.postgresql.org/docs/current/static/ddl-constraints.html