Refactor foreign key to fields

问题:

In PostgreSQL I need to refactor a table (Purchases); it has a foreign key to another table (Shop). Instead I want two fields that keep the relation in a textual way. I must NOT lose any information, the tables already contain data.

Purchases.shop_id: (long)          -- is the field I need to drop
Purchases.shop: (characters)       -- will hold the Shop's name
Purchases.shop_user: (characters)  -- will hold the Shop's user name.

Shop.id: (long, pk)      -- still referenced from Purchases
Shop.name: (characters)  -- Shop's name
Shop.user: (characters)  -- Shop's user name

Two fields are necessary because a Shop is unique on (name,user) (or by id of course).

ALTER TABLE Purchases ADD COLUMN shop CHARACTER VARYING(255);
ALTER TABLE Purchases ADD COLUMN shop_user CHARACTER VARYING(255);

-- ???

ALTER TABLE Purchases DROP CONSTRAINT shop_id_fk;
ALTER TABLE Purchases DROP COLUMN shop_id;

So the start and the ending is easy, can somebody help with middle-part? :)

I know that foreign keys were made for this but I have to do it this way.

回答1:

First of all: your change seems to go the wrong way. Your original, normalized schema is typically superior. If you need to display shop / user, create a VIEW.

But you may have your reasons, so here goes:

UPDATE purchases p
SET  (shop, shop_user) = (s.name, s."user")
FROM  shop s
WHERE s.id = p.shop_id;

You shouldn't be using the reserved word "user" as identifier.
And "name" is hardly ever a good name, either.
And there is hardly a good reason to use varchar(255) in Postgres (unlike SQL Server).

About varchar(255):

• Any downsides of using data type "text" for storing strings?
• More details in the manual.

回答2:

So it seems to me the piece you are missing is simply updating your purchases table to contain the information from your shop table. If that is correct then you could just update the table using the existing foreign key before you drop it:

UPDATE purchases SET (shop, shop_user) =
    (SELECT name, user FROM shop
     WHERE shop.id = purchases.shop_id);