Each foo must have a bar - Mailing list pgsql-general

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

Remember to vote!