Re: Each foo must have a bar

[David Fetter <david@xxxxxxxxxx>]

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

Remember to vote!