postgresql (Postgre)SQL 中的一对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9606843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
One-to-Many relationships in (Postgre)SQL
提问by Eric W.
I have two tables:
我有两个表：
posts:
帖子：
id | ... other stuff ... | tags
----+---------------------+--------------
1 | ... | <foo><bar>
2 | ... | <foo><baz><blah>
3 | ... | <bar><blah><goo>
and tags:
和标签：
tag
--------------
<foo>
<bar>
<baz>
<blah>
<goo>
posts.tags and tags.tag are both of type text. What I want is a relation from tags.tag to rows in posts such that querying <foo>would give me rows corresponding to posts 1 and 2, querying <blah>gives me 2 and 3, <bar>gives me 1 and 3, etc.
post.tags 和 tags.tag 都是文本类型。我想要的是从 tags.tag 到帖子中的行的关系，这样查询<foo>会给我对应于帖子 1 和 2 的行，查询<blah>给我 2 和 3，<bar>给我 1 和 3，等等。
I've looked at foreign keys, but I'm not sure it's what I want. (and honestly, I'm not exactly sure what it does). From what I can tell a foreign key must be equal to a primary key/unique column of a table. But what I want is all rows such that posts.tags ~ '.*<foo>.*', etc. I also want to be able to, say, get all tags that start with b, eg:
我看过外键，但我不确定这是我想要的。（老实说，我不确定它的作用）。据我所知，外键必须等于表的主键/唯一列。但我想要的是所有这样的行posts.tags ~ '.*<foo>.*'，等等。我也希望能够获得所有以 b 开头的标签，例如：
CREATE VIEW startswithB AS
SELECT tag
FROM tags
WHERE tag ~ '<b.*>';
SELECT DISTINCT * FROM posts, startswithB WHERE posts.tags ~ ('.*' || startswithB || '.*');
How do I get the relation I'm looking for? Is it possible?
我如何获得我正在寻找的关系？是否可以？
EDIT:
编辑：
Okay, what I've done:
好的，我做了什么：
create post_tags:
创建 post_tags：
SELECT posts.id, tags.tag
INTO post_tags
FROM posts, tags
WHERE posts.tags ~ ('.*' || tags.tag || '.*');
select all posts with tag <foo>:
选择所有带有标签的帖子<foo>：
SELECT *
FROM posts
WHERE posts.id IN (
SELECT id
FROM post_tags
WHERE tag = '<foo>'
);
回答by Daniel Lyons
What you actually have going on here is a many-to-many relationship. Think about it: each tag can be on several posts, and each post can have several tags.
您实际上在这里发生的是多对多关系。想一想：每个标签可以在多个帖子上，每个帖子可以有多个标签。
The correct relational architecture for this is to add another table in the middle like this:
正确的关系架构是在中间添加另一个表，如下所示：
CREATE TABLE post_tags (
id INTEGER REFERENCES posts,
tag VARCHAR REFERENCES tags
);
Then drop the tagscolumn on your posts table.
然后将tags列放在您的帖子表中。
This solves all your issues, because you can get the set of tags on a post or the set of posts with a given tag by joining against post_tags in different directions. You can also get the list of tags that start with something using a regular LIKE query, which will be more difficult if you have a bunch of strings concatenated in one field.
这解决了您的所有问题，因为您可以通过在不同方向上连接 post_tags 来获取帖子上的标签集或具有给定标签的帖子集。您还可以使用常规 LIKE 查询获取以某些内容开头的标签列表，如果您在一个字段中连接了一堆字符串，这将更加困难。
回答by Jason Iwinski
As Daniel mentioned, you have a many-to-many relationship. Just for clarification, here's how all 3 tables would look with a many-to-many setup:
正如丹尼尔提到的，你有一个多对多的关系。为了澄清起见，以下是所有 3 个表在多对多设置下的外观：
Posts:
帖子：
id | ... other stuff ...
---+---------------------
1 | ...
2 | ...
Tags:
标签：
tag
---
<foo>
<bar>
Post_Tags mapping table:
Post_Tags 映射表：
post_id | tag
--------+------
1 | <foo>
1 | <bar>
回答by Branko Dimitrijevic
Normalize your data model. Here is one way to represent the M:N relationship that you have:
规范化您的数据模型。这是表示您拥有的 M:N 关系的一种方法：
Please note that POST_TAG's PK is {POST_ID, TAG}, not just {POST_ID}.
请注意，POST_TAG 的 PK 是 {POST_ID, TAG}，而不仅仅是 {POST_ID}。
Finding all posts tagged with 'foo' would look like this:
查找所有标记为“foo”的帖子将如下所示：
SELECT *
FROM POST
WHERE
POST_ID IN (
SELECT POST_ID
FROM POST_TAG
WHERE TAG = 'foo'
)
For posts tagged with a tag that starts with 'f', you could do this:
对于带有以“f”开头的标签的帖子，您可以这样做：
SELECT *
FROM POST
WHERE
POST_ID IN (
SELECT POST_ID
FROM POST_TAG
WHERE TAG LIKE 'f%'
)
