重构字段的外键 [英] Refactor foreign key to fields
问题描述
在 PostgreSQL 中我需要重构一个表 (Purchases
);它有另一个表(Shop
)的外键.相反,我想要两个以文本方式保持关系的字段.我不能丢失任何信息,表格已经包含数据.
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
两个字段是必需的,因为商店在 (name,user)
上是唯一的(当然也可以通过 id
).
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.
推荐答案
您似乎走错了路.您的原始规范化架构通常更胜一筹.如果需要显示店铺/用户,创建一个VIEW
.
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;
不要使用保留字 用户"
作为标识符.
和名字"也几乎不是一个好的名字.
Postgres 中的 varchar(255)
通常表示误解.
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.
这篇关于重构字段的外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!