如何创建具有唯一组合主键的Postgres表？

Question

我在Postgres DB中有两个名为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)
);

如何确保仅将(winner, loser)或的唯一组合(loser, winner)用于matches主键，以便matches表不允许插入以下内容：

INSERT INTO matches VALUES (2, 1);

如果已经有包含VALUES (1, 2)like的行：

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

目的是避免相同玩家之间的比赛进入。

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包装器只是为了方便，只输入一次参数，在某些情况下是不必要的。