将外键重构为字段

问题描述

在 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

两个字段是必需的，因为 Shop 在 (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.

• 是否应该为 VARCHAR 列添加任意长度限制?
• 使用数据的任何缺点键入文本"；用于存储字符串?
• 手册中的更多详细信息.

这篇关于将外键重构为字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！