How to change foreign key values ​​in postgresql?

Question

Say I have two tables: Customer and City . There are many Customer who live in the same City . In cities there is a uid , which is the primary key. Customers have a link with foreign keys to their city through Customer.city_uid .

I need to exchange two City.uid for each other for external reasons. But customers must remain attached to their cities. Therefore, you must also change Customer.city_uid . So I thought that I first replaced City.uid and then changed Customer.city_uid according to the UPDATE -statement. Unfortunately, I cannot do this, as these uids refer to Customer -table, and PostgreSQL is stopping me from doing this.

Is there an easy way to replace two City.uid with each other, as well as Customer.city_uid s?

Answer 1

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; 

Answer 2

My instinct should recommend not trying to change the identifier field of the city table. But there is a lot of information. Therefore, this is more a feeling, not a final point of view.

Instead, I would change the values ​​in other fields of the city table. For example, change the name of city1 to the name city2 and vice versa.

For instance:

 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 identifier was not affected, but other values ​​were replaced. It is functionally the same as replacing identifiers, but with “softer touch” requests that do not require any changes to table restrictions, etc.)

Then in your linked 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 possible to repeat this update in all of these tables?

Answer 3

You can create two temporary cities.

You will have:

• City 1
• City 2
• City temperature 1
• City temperature 2

Then you can do the following:

• Update all UID clients from City 1 to City Temp 1.
• Update all UID clients from City 2 to City Temp 2.
• Exchange code 1 and 2 UIDs
• Move all customers back from City Temp 1 to City 1.
• Move all customers back from City Temp 2 to City 2.
• Delete temporary cities.