Postgresql – Complex `UNIQUE INDEX` scenario – Games, teams, players, positions!

indexpostgresqlunique-constraint

I have a table

CREATE TABLE (game, team, player, position);

If I create a unique index on (game, player, position) I get a table where in the same game, the same player can't play the same position for both teams. That's useful, but it's still possible for someone to insert a row where the same player plays two different positions, one on either team.

Is it possible to write some sort of unique index or constraint that makes it impossible for the same player to play for both teams in a game?

Best Answer

You could use an EXCLUDE constraint:

create extension btree_gist ;
create table foo (game text, team text, player text, position text, 
    exclude using gist (game with =, player with =, team with != ));

You will want to put the != column at the end of the list of columns, otherwise enforcing the constraint will be slower.

insert into foo values ('1', 'detroit', 'parish','catcher');

insert into foo values ('1', 'cubs', 'parish','LF');

ERROR:  conflicting key value violates exclusion constraint "foo_game_player_team_excl"
Related Question