11

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?

AME
  • 2,499
  • 5
  • 29
  • 45
  • 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: http://stackoverflow.com/a/18273069/633961 (But I am still looking for better ways to solve this). – guettli Oct 14 '16 at 07:39

4 Answers4

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;
doctore
  • 3,855
  • 2
  • 29
  • 45
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?

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you, however, that is not possible. I really need to change the uids. – AME Oct 15 '12 at 09:46
  • 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 Oct 15 '12 at 09:50
  • Added a visual example showing that swapping the name, etc, is functionally the same as swapping the ID field. – MatBailie Oct 15 '12 at 09:54
  • 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. – AME Oct 15 '12 at 09:59
  • 3
    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... – MatBailie Oct 15 '12 at 10:06
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:

  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.
Roger
  • 29
  • 6
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?

Eric Hanson
  • 596
  • 1
  • 5
  • 9