database design - psql swiss tournament system - prevent rematches between 2 player -

create table matches (   match_id bigserial primary key,   tournamentid integer,   winner_id integer,   loser_id integer check (winner_id != loser_id),   draw boolean,   foreign key(tournamentid, winner_id) references enroll(tournament,   player_id),   foreign key(tournamentid, loser_id) references enroll(tournament, player_id),   unique(tournamentid, winner_id, loser_id) ); 

this current match table schema. m trying figure out way prevent player 1 playing against player 2 twice. example have followings:

insert matches(tournamentid, winner_id, loser_id, draw) values('1', '2', '3', 'false');

insert matches(tournamentid, winner_id, loser_id, draw) values('1', '3', '2', 'false');

these 2 queries same , both allowed. question if there's way disallow second query?

i don't think can schema. dbmss allow subqueries in check constraints, could've been used here.

instead, can try:

create table matches (   match_id bigserial primary key,   tournamentid integer,   player1_id integer,   player2_id integer check (player1_id < player2_id),   result smallint check (result in (0, 1, 2)),   foreign key(tournamentid, player1_id) references enroll(tournament, player_id),   foreign key(tournamentid, player2_id) references enroll(tournament, player_id),   unique(tournamentid, player1_id, player2_id) ); 

where 0 indicates draw, 1 or 2 win corresponding player.