On Sat, Feb 11, 2006 at 02:59:48PM -0700, Michael Fuhr wrote:
> On Sat, Feb 11, 2006 at 12:56:36PM -0800, David Fetter wrote:
> > I'm trying to figure out how to enforce the following. Table foo
> > has a primary key. Table bar has a foreign key to foo. So far so
> > good. I'd also like to say, "for each row in foo, there must be
> > at least one row in bar."
>
> Possibly something involving CREATE CONSTRAINT TRIGGER, specifying
> DEFERRABLE INITIALLY DEFERRED? The documentation says it's not for
> general use; I've used it only in simple experiments so I'm not sure
> how problematic it could be. Anybody?
Kudos to Andrew at Supernews for helping me figure out what's below :)
Cheers,
D
CREATE TABLE foo (
id SERIAL PRIMARY KEY
);
CREATE TABLE bar (
id SERIAL PRIMARY KEY,
foo_id INTEGER NOT NULL REFERENCES foo(id)
ON DELETE CASCADE
DEFERRABLE
INITIALLY DEFERRED
);
CREATE FUNCTION foo_trg ()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
childless_foo_count INTEGER;
BEGIN
SELECT INTO
childless_foo_count
COUNT(*)
FROM
foo
LEFT JOIN
bar
ON (foo.id = bar.foo_id)
WHERE bar.foo_id IS NULL;
IF childless_foo_count > 0 THEN
RAISE EXCEPTION 'Each foo must have at least one bar.';
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER foo_after
AFTER INSERT OR UPDATE ON foo
FOR EACH ROW
EXECUTE PROCEDURE foo_trg();
CREATE TRIGGER bar_after
AFTER UPDATE OR DELETE ON bar
FOR EACH ROW
EXECUTE PROCEDURE foo_trg();
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
