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?

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;