Constraints that check other tables

Hi,

I've looked at CHECK constraints and briefly at the triggers section but
am a bit lost here.

I would like to have (the equivalent of) a CHECK to ensure a row in
another table exists and perhaps other before allowing an INSERT or UPDATE on a table.

Specifically, I have a join table teamsusers and savedteams,

CREATE TABLE teamsusers (
  team_id INTEGER NOT NULL REFERENCES teams,
  user_id INTEGER NOT NULL REFERENCES teams,
  is_leader CHAR(1) NOT NULL DEFAULT 'n' -- can be 'i', 'n', 'y'
);

CREATE TABLE savedteams (
  team_id INTEGER NOT NULL REFERENCES teams,
  user_id INTEGER NOT NULL REFERENCES teams,
  CONSTRAINT savedteams_pkey PRIMARY KEY (team_id, user_id)
);

(It occurs to me as I write this that teamsusers doesn't have the same
PK; it uses a sequence -- at least one part of my question could be
solved with a REFERENCES.)

So an entry drops into savedteams when a user wishs to mark a team
saved. (I realize I could have a flag in teamsusers but for the purposes
of this I'd like to be educated :)

What I'd like is to only allow that INSERT when a corresponding row with
user_id and team_id exists AND (for that row) is_leader='y'

I'm not familiar yet with triggers, rules or CHECKs beyond simple
same-table comparisons, but will happily read...

Many thanks, Paul


--
Paul Makepeace ................................  http://paulm.com/ecademy

"If I ask for help, will you be kind and help me, then my eyeballs will
 pop out!"
   -- http://paulm.com/toys/surrealism/