SQL - Proper way to create unique pairs of foreign keys-CodePudding

Suppose we have this "matchmaking" logic, where we want to store the matches between users. I created this schema for the table and wanted to be sure if this is the correct way to store that information.

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name VARCHAR (255) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS matches (
    id INTEGER PRIMARY KEY,
    a INTEGER NOT NULL CHECK (a < b) REFERENCES users (id),
    b INTEGER NOT NULL CHECK (a < b) REFERENCES users (id),
    frequency INTEGER NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX IF NOT EXISTS matches_a_b_uidx ON matches (a, b);

I want to make sure that there are no duplicates of matches, so if a=1 and b=2, we cannot have a=2 and b=1, as they are the same. Also, no matches with themselves.

Is this duplicate CHECK (a < b) the correct way to structure this kind of logic?

CodePudding user response：

Instead of the two column constraints, have one table constraint:

CREATE TABLE IF NOT EXISTS matches (
    id INTEGER PRIMARY KEY,
    a INTEGER NOT NULL REFERENCES users (id),
    b INTEGER NOT NULL REFERENCES users (id),
    frequency INTEGER NOT NULL DEFAULT 0,
    CHECK (a < b)
);