如何在 postgres 数据库上切换两个 ID [PK]? [英] How do I switch two ID [PK] on postgres database?
问题描述
我想更改 Postgres 上两行的 ID，以切换它们.它们已经被定义为外键，所以我不能使用第三个数字来进行切换.
I want to change the ID on two rows on Postgres, to switch them. They are already defined as foreign key so I cannot use a third number to do the switch.
如何在一个 SQL 查询或事务中执行此操作?
How can I do this in one SQL query or transaction?
示例:
UPDATE mytable SET id=2 WHERE ID=1;
UPDATE mytable SET id=1 WHERE ID=2
推荐答案
您提到了外键，但尚不清楚 id 是外键约束的引用列还是引用列.
You mention foreign keys, but it remains unclear whether id is the referenced or the referencing column of a foreign key constraint.
>
如果 id 是引用列，您只需定义 fk 约束 ON UPDATE CASCADE.然后，您可以根据需要随意更改 id.更改级联到相关列.
If id is the referenced column you just define the fk constraint ON UPDATE CASCADE. Then you can change your id as much as you want. Changes are cascaded to the depending columns.
如果 id 是引用列(没有其他外键约束指向它)，那么自 PostgreSQL 9.0 以来还有另一种更快的方法强>.您可以使用可延迟的主键或唯一键.考虑以下演示:
If id is the referencing column (an no other foreign key constraints point to it), then there is another, faster way since PostgreSQL 9.0. You can use a deferrable primary or unique key. Consider the following demo:
注意，如果您想使用另一个表中的外键约束引用id，则不能使用它.我引用手册这里:
Note that you cannot use this if you want to reference id with a foreign key constraint from another table. I quote the manual here:
被引用的列必须是不可延迟唯一的列或引用表中的主键约束.
The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.
测试平台:
CREATE TEMP TABLE t
( id integer
,txt text
,CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO t VALUES
(1, 'one')
,(2, 'two');
更新:
UPDATE t
SET id = t_old.id
FROM t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));
结果:
SELECT * FROM t;
id | txt
---+-----
2 | one
1 | two
您还可以声明约束 DEFERRABLE INITIALLY IMMEDIATE 并在同一事务中使用 SET CONSTRAINTS ... DEFERRED.
You can also declare the constraint DEFERRABLE INITIALLY IMMEDIATE and use SET CONSTRAINTS ... DEFERRED in the same transaction.
请务必阅读手册中的详细信息:
Be sure to read about the details in the manual:
甚至似乎可以使用 DEFERRABLE INITIALLY IMMEDIATE 而没有 SET CONSTRAINTS.我发布了一个关于此的问题.
Even seems to work with DEFERRABLE INITIALLY IMMEDIATE and no SET CONSTRAINTS. I posted a question about that.
这篇关于如何在 postgres 数据库上切换两个 ID [PK]?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
