title: Is there a SQL table constraint to ensure that a relationship exists between two columns?
body: <p>I'm trying to solve the following problem.</p>
  
  <pre><code>CREATE TABLE leagues (
      id SERIAL PRIMARY KEY,
      name TEXT,
      ...
  );
  
  CREATE TABLE players (
      id SERIAL PRIMARY KEY,
      first_name TEXT,
      last_name TEXT,
      ...
  );
  
  CREATE TABLE league_members (
      league_id INTEGER REFERENCES leagues (id),
      player_id INTEGER REFERENCES players (id),
      PRIMARY KEY (league_id, player_id)
  );
  
  CREATE TABLE games (
      id SERIAL PRIMARY KEY,
      league_id INTEGER REFERENCES leagues (id),
      winner INTEGER REFERENCES players (id),
      loser INTEGER REFERENCES players (id)
  );
  </code></pre>
  
  <p>In the games table, is there a constraint I can use that will check to make sure that the winner and loser are both members of the same league?</p>
  
  <p>For example:</p>
  
  <p>players table:</p>
  
  <pre><code>---- id ---- first_name ----
       1       "John"
       2       "Joe"
       3       "Jill"
  </code></pre>
  
  <p>leagues table:</p>
  
  <pre><code>---- id ---- name ----
       1       "League 1"
       2       "League 2"
  </code></pre>
  
  <p>league_members table:</p>
  
  <pre><code>---- id ---- league_id ---- player_id ----
       1           1              1
       2           1              2
       3           2              3
  </code></pre>
  
  <p>I want to make sure that in the games table the following would cause an error:</p>
  
  <pre><code>INSERT INTO games (league_id, winner, loser) VALUES (1,1,3);
  </code></pre>
  
  <p>This shouldn't be allowed since player 3 isn't in league 1. Right now I'm just running this query on the league_members table:</p>
  
  <pre><code>SELECT count(*) FROM league_members WHERE league_id = 1 AND (player_id = 1 OR player_id = 3);
  </code></pre>
  
  <p>and them making sure count is 2, which means both players are members of the league with id 1. If the count is 2 then I allow the insertion into the games table, otherwise I don't. Is there a way to do this but have the database handle the constraint so that I don't have to do the extra query before I try to insert into the games table?</p>
  
  <p>Thanks!</p>