PostgreSQL-正确更改表行的ID [英] PostgreSQL - properly change ID of table row

问题描述

如何更改某个表的行的ID？

就像：

 更新表SET id = 10 WHERE id = 5; 
  

但是通过这种方式，它将级联更改为引用该ID的其他表？ / p>

我想这样做，因为我需要从另一个具有大多数相同表但ID不同的数据库中导入数据。因此，如果id与旧数据库匹配，则可以更轻松地正确导入数据。

解决方案

假设您有以下两个表：

 创建引用的表（id整数主键）； 
创建表引用程序（引用的整数引用（id））； 
  

表引用程序引用的表：

  => \d referencer 
表 public.referencer 
列|类型修饰符
 -------- + --------- + ----------- 
 a |整数| 
外键约束：
 referencer_a_fkey外键（a）引用的参考（id）
  

然后在两个值中插入一个值：

 插入参考值（1）; 
插入参考值（1）; 
 
 select * 
从
引用rd 
内部联接
引用者rd.id = rr.a 
; 
 id | a 
 ---- + --- 
 1 | 1 
  

现在您想在更新级联上将引用更改为 ：

 更改表引用程序
删除约束referencer_a_fkey，
添加外键（a）在更新级联上引用的参考（id）； 
  

并对其进行更新：

 更新引用集ID = 2; 
 
 select * 
从
引用rd 
内部联接
引用者rd.id = rr.a 
; 
 id | a 
 ---- + --- 
 2 | 2 
  

现在，如果已更新的ID已经存在，则在引用表主键中会有另一个问题。但这会引起另一个问题。

UPDATE

这很危险，因此请首先备份数据库。必须以超级用户身份完成：

  update pg_constraint 
 set confupdtype ='c'
其中conname在（
从
中选择
 c.conname 
 pg_constraint c 
内部连接
 pg_class在referenced上引用.oid = c.confrelid 
其中
 referenced.relname ='已引用'
和
 c.contype ='f'
）; 
  

它将在更新时将引用表上的所有外键约束更改为级联

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.

解决方案

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

这篇关于PostgreSQL-正确更改表行的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！