PostgreSQL - 正确更改表行的 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15267050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - properly change ID of table row
提问by Andrius
How to change id of some table's row?
如何更改某个表行的ID？
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?
但是，它会以何种方式将更改级联到每个其他使用该 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.
我想这样做，因为我需要从另一个数据库中导入数据，该数据库具有大多数相同的表，但 ID 不同。因此，如果 ids 与旧数据库匹配，则正确导入数据会更容易。
回答by Clodoaldo Neto
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:
现在您想将引用更改为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.
现在，如果更新的 id 已经存在，则引用的表主键中将出现另一个问题。但这会产生另一个问题。
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
它将引用表上的所有外键约束更改为 on update cascade
回答by Artegon
You will need to change your foreign key and set ON UPDATEaction to CASCADE. When you change a value, all associated values will be changed too.
您需要更改外键并将ON UPDATE操作设置为CASCADE. 当您更改一个值时，所有关联的值也将更改。
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
有关更多信息，请参阅http://www.postgresql.org/docs/current/static/ddl-constraints.html
