Postgres：从 A 到 B 的唯一引用

发布于 2024-08-02 06:50:30 字数 525 浏览 15 评论 0原文

我想要 (tag1, tag2) 和 tag_id 对之间的双射。

CREATE TABLE tags (
         question_id INTEGER NOT NULL,
         tag_id SERIAL NOT NULL,
         tag1 VARCHAR(20),
         tag2 VARCHAR(20),
         PRIMARY KEY(question_id, tag_id),
         (tag1, tag2) UNIQUE references tags(tag_id)          #How?
     );

我不想要任何引用，例如：

(PHP, Perl) points to 1 and 2,
3 points to (C#, null) and (Python, Elinks)

换句话说，我希望引用从 (tag1, tag2) 到标签 (tag_id) 是唯一的，而不是唯一的 (tag1, tag2)。

原文

I want a bijection between the pair (tag1, tag2) and tag_id.

CREATE TABLE tags (
         question_id INTEGER NOT NULL,
         tag_id SERIAL NOT NULL,
         tag1 VARCHAR(20),
         tag2 VARCHAR(20),
         PRIMARY KEY(question_id, tag_id),
         (tag1, tag2) UNIQUE references tags(tag_id)          #How?
     );

I want no reference such as:

(PHP, Perl) points to 1 and 2,
3 points to (C#, null) and (Python, Elinks)

In other words, I want the REFERENCE to be unique FROM (tag1, tag2) TO tags(tag_id), not UNIQUE(tag1, tag2).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

与酒说心事 2024-08-09 06:50:30

这可能更像您正在寻找的内容：

CREATE TABLE tags (
    question_id INTEGER NOT NULL,
    tag_id SERIAL NOT NULL,
    tag1 VARCHAR(20),
    tag2 VARCHAR(20),
    PRIMARY KEY (tag_id),
    INDEX (question_id),
    UNIQUE (tag1, tag2)
);

将“tag_id”设为主键意味着您只能拥有一个具有给定“tag_id”的条目，并且基于“tag_id”的搜索速度会很快。

“question_id”上的索引将提高基于“question_id”的搜索速度，这就是我认为您试图对原始主键定义执行的操作。如果您确实希望 (tag_id, Question_id) 对像您所拥有的那样是唯一的，则在其中添加一个 UNIQUE (tag_id, Question_id) ，但我想说您应该将 tag_id 保留为主键。

(tag1, tag2) 上的唯一性约束可防止反向映射出现重复项。

以下是一些可行的示例：

可行：

1 -> (x，y)

2→ (x, z)

失败（tag_id 是主键，因此是唯一的）：

1 -> (x，y)

1→ (y, x)

失败（(tag1, tag2) 对不唯一）：

1 -> (x，y)

2→ (x, y)

然而，(x, y) 不等于(y, x) 对。我不知道如何捕捉唯一性约束。

This might be more like what you are looking for:

CREATE TABLE tags (
    question_id INTEGER NOT NULL,
    tag_id SERIAL NOT NULL,
    tag1 VARCHAR(20),
    tag2 VARCHAR(20),
    PRIMARY KEY (tag_id),
    INDEX (question_id),
    UNIQUE (tag1, tag2)
);

Making 'tag_id' the primary key means that you can only have one entry with a given 'tag_id', and that searches based on 'tag_id' will be fast.

The index on 'question_id' will improve search speed based on 'question_id', which is what I think you were trying to do with your original PRIMARY KEY definition. If you really want the (tag_id, question_id) pair to be unique, as you had it, then add a UNIQUE (tag_id, question_id) in there, but I would say that you should leave tag_id as the primary key.

The uniqueness constraint on (tag1, tag2) prevents the reverse mapping from having duplicates.

Here are a few examples of what can work:

Works:

1 -> (x, y)

2 -> (x, z)

Fails (tag_id is a primary key, and therefore is unique):

1 -> (x, y)

1 -> (y, x)

Fails (the pair (tag1, tag2) is not unique):

1 -> (x, y)

2 -> (x, y)

However, the pair (x, y) is not equal to the pair (y, x). I'm not sure how to catch that uniqueness constraint.