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/
