Refactor foreign key to fields
database-design, foreign-keys, postgresql, sql
Solution
Your seem 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;
Don't use the reserved word `"user"` as identifier. And "name" is hardly ever a good name, either. And `varchar(255)` in Postgres typically indicates a misunderstanding.
About `varchar(255)`:
- Should I add an arbitrary length limit to VARCHAR columns?
- Any downsides of using data type "text" for storing strings?
- More details in the manual.
Problem
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.