Search Postgresql Archives

Re: Each foo must have a bar

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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)




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux