Re: Each foo must have a bar

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(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!