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

On Feb 12, 2006, at 6:59 , 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?

I've used constraint triggers to handle multi-statement updates for
temporal tables. I also haven't had any in production but am planning
to soon. I have tested it quite a bit and haven't seen any problems.
Caveat being that I tend to have small databases, so I'm not sure
about the performance of the PL/pgSQL function I use to enforce the
assertion. I suspect the except should be more performant than the
count, but that's speculation.

I don't know if the SQL spec allows for statements with multiple
updates, e.g. something like

insert into foo (foo) values ('blurfl'), -- note comma
insert into bar (bar, foo_id)
select 'bat', foo_id
from foo where id = 'blurfl';

(And I definitely don't know if that kind of recursive assignment
would even work; maybe it would have to be more like:

insert into foo (foo_id, foo) values (1, 'blurfl'),
insert into bar (bar, foo_id) values ('bat', 1);
)

That might help get rid of the need to use a constraint trigger
rather than a normal assertion. Not that PostgreSQL has assertions
yet anyway :)

Please find an example below. Hope this helps.

Michael Glaesemann
grzm myrealbox com

create table foo
(
     foo_id serial primary key
     , foo text not null unique
);

create table bar
(
     bar_id serial primary key
     , bar text not null unique
     , foo_id integer not null
         references foo (foo_id)
         on update cascade on delete cascade
);

create function assert_bar_for_each_foo()
returns trigger
language plpgsql as $$
begin
     if exists (
         select foo_id
         from foo
         except
         select foo_id
         from bar
     )
     then raise exception 'Every foo must have a bar';
     end if;
     return null;
end;
$$;

create constraint trigger assert_bar_for_each_foo_tr
after insert on foo
-- With the on update on delete cascade I don't believe you
-- need to check on update or delete here.
initially deferred
for each row
execute procedure assert_bar_for_each_foo();

create constraint trigger assert_bar_for_each_foo_tr
after delete on bar
-- The foreign key on bar takes care of inserts and updates.
initially deferred
for each row
execute procedure assert_bar_for_each_foo();

insert into foo (foo) values ('blurfl');
ERROR:  Every foo must have a bar

begin;
insert into foo (foo) values ('blurfl');
INSERT 0 1
insert into bar (bar, foo_id)
INSERT 0 1
select 'bat', foo_id
from foo
where foo = 'blurfl';
commit;

select *
from foo
natural join bar;
foo_id |  foo   | bar_id | bar
--------+--------+--------+-----
       2 | blurfl |      1 | bat
(1 row)

update foo
set foo_id = 3
where foo = 'blurfl';
UPDATE 1

select *
from foo
natural join bar;
foo_id |  foo   | bar_id | bar
--------+--------+--------+-----
       3 | blurfl |      1 | bat
(1 row)

update bar
set foo_id = 2
where bar = 'bat';
ERROR:  insert or update on table "bar" violates foreign key
constraint "bar_foo_id_fkey"
DETAIL:  Key (foo_id)=(2) is not present in table "foo".

delete from bar where bar = 'bat';
ERROR:  Every foo must have a bar

delete from foo where foo = 'blurfl';
DELETE 1

select * from foo;
foo_id | foo
--------+-----
(0 rows)

select * from bar;

bar_id | bar | foo_id
--------+-----+--------
(0 rows)

select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.1.0 on powerpc-apple-darwin8.3.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc.
build 5026)
(1 row)