sql – 重写字段的外键

在PostgreSQL中我需要重构一个表(Purchases);它有另一个表(Shop)的外键.相反,我想要两个以文本方式保持关系的字段.我不能丢失任何信息,表格已包含数据.

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

两个字段是必需的,因为Shop在(名称,用户)上是唯一的(或者当然是id).

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;

所以开始和结束很容易,有人可以帮助中间部分吗？