重新设计外键到字段 [英] Refactor foreign key to fields

问题描述

在PostgreSQL中，我需要重构一个表（ Purchase ）;它有一个外键到另一个表（ 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在名称，用户）（或当然是 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.

推荐答案

首先：你的改变似乎是错误的。您的原始标准化模式通常较高。如果您需要显示商店/用户，请创建 查看 。

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;

您不应该使用保留字 user作为标识符。

和名字几乎不是一个好的名字，

而且，使用 varchar（255）几乎没有什么好的理由Postgres（不像SQL Server）。

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).

• 使用数据类型文本的任何缺点用于存储字符串？


• 手册中的详细信息。

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

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