Each foo must have a bar

Folks,

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."

I've tried the following, but the check fails too soon. I also tried
an INITIALLY DEFERRED foreign key on bar to foo, trying the INSERT on
bar first, but that didn't work in transaction_isolation LEVEL
SERIALIZABLE.

Any clues?

Cheers,
D

CREATE TABLE foo (
id SERIAL PRIMARY KEY
);

CREATE TABLE bar (
foo_id INTEGER NOT NULL REFERENCES foo(id)
ON DELETE CASCADE
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 STATEMENT
EXECUTE PROCEDURE foo_trg();

--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!