如何更改 postgresql 中的外键值？

How to change values of foreign keys in postgresql?

提问by AME

Let's say I have two tables: Customerand City. There are many Customers that live in the same City. The cities have an uidthat is primary key. The customers have a foreign key reference to their respective city via Customer.city_uid.

假设我有两个表：Customer和City。有许多Customers 住在同一个City. 城市有一个uid是主键。客户通过Customer.city_uid.

I have to swap two City.uids with one another for external reasons. But the customers should stay attached to their cities. Therefore it is necessary to swap the Customer.city_uids as well. So I thought I first swap the City.uids and then change the Customer.city_uids accordingliy via an UPDATE-statement. Unfortunately, I can not do that since these uidsare referenced from the Customer-table and PostgreSQL prevents me from doing that.

City.uid由于外部原因，我必须相互交换两个s。但客户应该与他们的城市保持联系。因此，也有必要交换Customer.city_uids。所以我想我首先交换City.uids，然后Customer.city_uid通过 -UPDATE语句相应地更改s 。不幸的是，我不能这样做，因为这些uids是从Customer-table引用的，而 PostgreSQL 阻止我这样做。

Is there an easy way of swapping the two City.uids with one another as well as the Customer.city_uids?

有没有一种简单的方法可以将两个City.uids 和 s 相互交换Customer.city_uid？

回答by doctore

One solution could be:

一种解决方案可能是：

BEGIN;
1. Drop foreign key
2. Make update
3. Create foreign key
COMMIT;

Or:

或者：

BEGIN;
1. Insert "new" correct information
2. Remove outdated information
COMMIT;

回答by Roger

You could create two temporary cities.

你可以创建两个临时城市。

You would have:

你将会拥有：

• City 1
• City 2
• City Temp 1
• City Temp 2

• 城市 1
• 城市2
• 城市温度 1
• 城市温度 2

Then, you could do the follow:

然后，您可以执行以下操作：

1. Update all Customer UIDs from City 1 to City Temp 1.
2. Update all Customer UIDs from City 2 to City Temp 2.
3. Swap City 1 and 2 UIDs
4. Move all Customers back from City Temp 1 to City 1.
5. Move all Customers back from City Temp 2 to City 2.
6. Delete the temporally cities.

1. 将所有客户 UID 从 City 1 更新为 City Temp 1。
2. 将所有客户 UID 从 City 2 更新为 City Temp 2。
3. 交换城市 1 和 2 UID
4. 将所有客户从 City Temp 1 移回 City 1。
5. 将所有客户从 City Temp 2 移回 City 2。
6. 删除临时城市。

回答by MatBailie

My instinct is to recommend not trying to change the citytable's id field. But there is lot of information missing here. So it really is a feelingrather than a definitivepoint of view.

我的直觉是建议不要尝试更改city表的 id 字段。但是这里缺少很多信息。因此，这确实是一种感觉，而不是确定的观点。

Instead, I would swap the valuesin the other fieldsof the citytable. For example, change the name of city1 to city2's name, and vice-versa.

相反，我会交换表的其他字段中的值。例如，将city1 的名称更改为city2 的名称，反之亦然。city

For example:

例如：

OLD TABLE                               NEW TABLE

 id | name  | population                  id | name  | population
-------------------------                -------------------------
  1 | ABerg | 123456                       1 | BBerg | 654321
  2 | BBerg | 654321                       2 | ABerg | 123456
  3 | CBerg | 333333                       3 | CBerg | 333333

(The ID was not touched, but the other values were swapped. Functionally the same as swapping the IDs, but with 'softer touch' queries that don't need to make any changes to table constraints, etc.)

（未触及 ID，但交换了其他值。在功能上与交换 ID 相同，但具有不需要对表约束等进行任何更改的“软接触”查询。）

Then, in your associated tables, you can do...

然后，在您的关联表中，您可以执行...

UPDATE
  Customer
SET
  city_uid = CASE WHEN city_uid = 1 THEN 2 ELSE 1 END
WHERE
  city_uid IN (1,2)

But then, do you have other tables that reference city_uid? And if so, is it feasible for you to repeat that update on all those tables?

但是，您还有其他参考表city_uid吗？如果是这样，您是否可以在所有这些表上重复该更新？

回答by Eric Hanson

You can also add an ON UPDATE CASCADEclause to the parent table's CREATE TABLEstatement, as described here:

您还可以ON UPDATE CASCADE向父表的CREATE TABLE语句添加子句，如下所述：

How to do a cascading update?

如何进行级联更新？