Geek Answers Handbook

PostgreSQL - change table row id correctly

How to change the identifier of some table row?

how

UPDATE table SET id=10 WHERE id=5;

But how will it cascade changes in every other table that refers to this table with this identifier?

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

+6
source share
2 answers

Suppose you have these two tables:

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

Link table link table links:

 => \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 the 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 link 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

You will now have another problem in the primary key of the table referenced table if the updated identifier already exists. But this will create another question.

UPDATE

This is dangerous, so back up your db first. This should be done as root:

 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 foreign key constraints in the reference table to on update cascade

+8
source

You will need to change the foreign key and set ON UPDATE to CASCADE . If you change the value, all related values ​​will also be changed.

This is an example of 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

+1
source

More articles:


All Articles