How to change values of foreign keys in postgresql?

Question

Let's say I have two tables: Customer and City. There are many Customers that live in the same City. The cities have an uid that is primary key. The customers have a foreign key reference to their respective city via 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 uids are referenced from the Customer-table and PostgreSQL prevents me from doing that.

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

The problem gets bigger if you have N tables which reference the "master" (in your case City) table. Up to now, this is my favourite solution: stackoverflow.com/a/18273069/633961 (But I am still looking for better ways to solve this). — guettli
– guettli, Commented Oct 14, 2016 at 7:39

user731136 · Accepted Answer · 2012-10-15 09:52:54Z

7

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;

answered Oct 15, 2012 at 9:52

user731136

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2012-10-15 09:53:52Z

1

My instinct is to recommend not trying to change the city table's id field. But there is lot of information missing here. So it really is a feeling rather than a definitive point of view.

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

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.)

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?

edited Oct 15, 2012 at 9:53

answered Oct 15, 2012 at 9:42

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

5 Comments

AME Over a year ago

Thank you, however, that is not possible. I really need to change the uids.

MatBailie Over a year ago

Could you explain more? If uid = 1 is now associated with the city name, etc, of what uid = 2 used to have, then haven't you done the same thing? In what aspect does this fall short? Just so that we can propose alternative solutions.

MatBailie Over a year ago

Added a visual example showing that swapping the name, etc, is functionally the same as swapping the ID field.

AME Over a year ago

The city has more aspects to it, than just a name, and there are further external dependencies. There will be no way around changing the uids.

MatBailie Over a year ago

If there are additional fields, you swap all of them, except the ID field. This is functionally identical to swapping the ID field. Literally, you will not be able to see the difference in the city table between the two approaches. The only difference in process is that you don't need to try to update a primary key field. If there are other dependencies in other tables, these will present themselves regardless of which approach you take. Sorry If I'm missing something, but I've had what appears to be the same requirement myself, and this is what I did, and it worked...

Roger · Accepted Answer · 2018-02-21 18:52:13Z

1

You could create two temporary cities.

You would have:

City 1
City 2
City Temp 1
City Temp 2

Then, you could do the follow:

Update all Customer UIDs from City 1 to City Temp 1.
Update all Customer UIDs from City 2 to City Temp 2.
Swap City 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 the temporally cities.

answered Feb 21, 2018 at 18:52

Roger

296 bronze badges

Comments

Eric Hanson · Accepted Answer · 2020-05-18 15:29:34Z

1

You can also add an ON UPDATE CASCADE clause to the parent table's CREATE TABLE statement, as described here:

How to do a cascading update?

answered May 18, 2020 at 15:29

Eric Hanson

6541 gold badge7 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to change values of foreign keys in postgresql?

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related