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?

  • I have no idea what to title this thing so I mentioned HAVING as that's how I'd solve this problem in a SELECT.
    – ldrg
    Oct 12 '17 at 4:57
  • 1
    Is this a round-robin scenario where squads are being picked and reformed regularly? If not, then (team, player) might be your UNIQUE index. If so, then all four fields will be required. What about subbing on and off - more difficult again! :-)
    – Vérace
    Oct 12 '17 at 5:42
  • 1
    I've changed the title to better (IMHO) reflect what you're asking. If you disagree, please feel free to change it back!
    – Vérace
    Oct 12 '17 at 8:41

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"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.