如何创建一个拥有唯一联合主键的Postgres表？

Question

我在Postgres数据库中有两个表，分别命名为players和matches，具体如下：

CREATE TABLE players (
    name text NOT NULL,
    id serial PRIMARY KEY
);

CREATE TABLE matches (
    winner int REFERENCES players (id),
    loser int REFERENCES players (id),
    -- to prevent rematch btw players
    CONSTRAINT unique_matches
    PRIMARY KEY (winner, loser)
);

如何确保只有唯一的组合可以用于匹配主键，这组合要么是 (胜利者，失败者)，要么是 (失败者，胜利者)，以使 matches 表不允许插入以下内容：

INSERT INTO matches VALUES (2, 1);

如果已经有一行包含VALUES (1, 2)，例如：

 winner | loser
--------+-------
      1 |     2

目标是避免同一玩家之间的比赛匹配。

- Babak K

Answer 1

创建唯一索引：

CREATE UNIQUE INDEX matches_uni_idx ON matches
   (greatest(winner, loser), least(winner, loser));

由于这些仅适用于列而不是表达式，因此无法成为UNIQUE或PRIMARY KEY约束条件。

您可以添加一个serial列作为PK，但是只有两个整数列的情况下，您的原始PK也非常高效（请参见注释）。它还会自动使两个列NOT NULL。（否则，请添加NOT NULL限制）

您还可以添加一个CHECK约束条件来排除玩家与自己对战：

CHECK (winner <> loser)

提示：要搜索一对ID（其中你不知道谁赢了），请将相同的表达式构建到查询中，索引将被使用：

SELECT * FROM matches
WHERE  greatest(winner, loser) = 3  -- the greater value, obviously
AND    least(winner, loser) = 1;

如果你处理未知参数，而事先不知道哪个更大：

WITH input AS (SELECT $id1 AS _id1, $id2 AS _id2)  -- input once
SELECT * FROM matches, input
WHERE  greatest(winner, loser) = greatest(_id1, _id2)
AND    least(winner, loser) = least(_id1, _id2);

CTE包装器只是为了方便一次输入参数，而在某些情况下并不必要。

- Erwin Brandstetter

---

谢谢@Erwin。它像魔法一样奏效;) 但是，您建议使用serial列作为PK而不是我之前的方式（PRIMARY KEY(winner, loser)）有什么原因吗？再次感谢:) - Babak K

@BabakK：你说得对，PRIMARY KEY (winner, loser) 这样做是可行的。而且，使用两个整数列也非常高效。如果您在表中有 FK 引用，那么使用单列代理 PK 可能更简单。 - Erwin Brandstetter

Answer 2

Postgres不支持对表达式的约束，因此我想不到一种直接表达此要求的约束方式。但你可以改变表的结构，使其有两列用于比赛的选手（主键），一个约束条件确保player1始终具有两者中较小的id，并添加另一列表示获胜者：

CREATE TABLE matches (
    p1 int REFERENCES players (id),
    p2 int REFERENCES players (id),
    p1winner boolean,

    CONSTRAINT matches_pk PRIMARY KEY (p1, p2),
    CONSTRAINT matches_players_order CHECK (p1 < p2)
);

- Mureinik