CODEWITHSUNDEEP
sqlpostgresqlforeign-key-relationship
AME
AME

Reputation: 2609

How to change values of foreign keys in postgresql?

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?

Upvotes: 12

Views: 31554

Answers (4)

Eric Hanson
Eric Hanson

Reputation: 654

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?

Upvotes: 1

Roger
Roger

Reputation: 29

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:

  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.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86716

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?

Upvotes: 1

user731136
user731136

Reputation:

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;

Upvotes: 7

Related Questions