How to create a Postgres table with unique combined primary key?

Question

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.

Erwin Brandstetter · Accepted Answer · 2015-11-14T13:42:11.660

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.

Thanks @Erwin. it worked like a charm ;) however, is there a reason that you suggested to use a `serial` column to serve as PK as opposed to the way I previously had (`PRIMARY KEY (winner, loser)`)?Thanks again :) — Babak K, Nov 14 '15 at 05:48
@BabakK: You are right, `PRIMARY KEY (winner, loser)` would work this way. And with two integer columns, it's very efficient, too. If you have FK references to the table it might be simpler to use a single-column surrogate PK. — Erwin Brandstetter, Nov 14 '15 at 13:32

score 1 · Answer 2 · answered Nov 14 '15 at 05:28

Postgres doesn't support constraints on expressions, so I can't think of a direct way of expressing this requirement as a constraint. One thing you could do, though, is change the table's structure so it has two columns for the players of the match (the primary key), a constraint that ensures that player1 always has the smaller id of the two and an additional column to indicate the winner:

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

How to create a Postgres table with unique combined primary key?

2 Answers2

Linked