I haven't seen this hit the lists yet, though I sent it nearly 12
hours ago. Resending for completeness.
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)
