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?