如何创建具有唯一组合主键的Postgres表？ [英] How to create a postgres table with unique combined primary key?

问题描述

我有两张名为球员的表格&在Postgres数据库中匹配，如下所示：

  CREATE TABLE玩家（
名称文本NOT NULL，
 id serial PRIMARY KEY 
）; 
 
 CREATE TABLE matches（
 winner int REFERENCES players（id），
 loser int参考玩家（id），
  - 防止重新配对btw玩家
 CONSTRAINT unique_matches 
 PRIMARY KEY（获胜者，输家）
）; 
  

如何确保只有（获胜者，失败者）的唯一组合）或（失败者，获胜者）用于匹配主键，以便 match 表将不允许插入：

  INSERT INTO matches VALUES （2，1）; 
  

如果它已经有一行包含 VALUES（1，2） code> like：

  winner |失败者
 -------- + ------- 
 1 | 2 
  

目标是避免同一玩家之间进行比赛。

解决方案

创建唯一索引：

 创建独特索引matches_uni_idx ON匹配
（最大（赢家，输家），最少（赢家，输家））; 
  

不能是 UNIQUE 或 PRIMARY KEY 约束，因为这些只能使用列，而不是表达式。

您可以添加 serial 列作为PK，但只有两个整数列，您的原始PK也非常高效（见评论）。并且它自动使两列 NOT NULL 。 （否则，添加 NOT NULL 约束。）

您还可以添加 CHECK 约束以排除玩家对自己的反应：

  CHECK（获胜者>失败者）$ b提示：要搜索一对ID（您不知道谁赢了），建立相同的ID（$ b  

表达式进入您的查询，索引将被使用：

  SELECT * FROM matches 
 WHERE最伟大的（赢家，输家）= 3  - 更大的值，显然
 AND最小（胜者，失败者）= 1; 
  

如果您处理未知参数，并且您不知道哪个更早提前：输入AS（SELECT $ id1 AS _id1，$ id2 AS _id2） - 输入一次
SELECT * FROM matches，（$）

输入
 WHERE最大（优胜者，输家）=最大（_id1，_id2）
 AND最小（胜者，输家）=最少（_id1，_id2）; 
  

CTE包装器只是为了方便一次输入参数，在某些上下文中不需要。 p>

I have two tables named players & matches in a Postgres DB as follows:

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

How can I ensure that only a unique combination of either (winner, loser) or (loser, winner) is used for matches primary key so that the matches table won't allow the insertion of:

INSERT INTO matches VALUES (2, 1);

If it already has a row containing VALUES (1, 2) like :

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

The goal is to avoid entry of matches between the same players.

解决方案

Create a unique index:

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

Can't be a UNIQUE or PRIMARY KEY constraint, since those only work with columns, not expressions.

You might add a serial column to serve as PK, but with just two integer columns, your original PK is very efficient, too (see comments). And it makes both columns NOT NULL automatically. (Else, add NOT NULL constraints.)

You also might add a CHECK constraint to rule out players playing against themselves:

CHECK (winner <> loser)

Hint: To search for a pair of IDs (where you don't know who won), build the same expressions into your query, and the index will be used:

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

If you deal with unknown parameters and you don't know which is greater ahead of time:

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

The CTE wrapper is just for convenience to enter parameters once only and not necessary in some contexts.

这篇关于如何创建具有唯一组合主键的Postgres表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！