Thread: Each foo must have a bar
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!
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? -- Michael Fuhr
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@fetter.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
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)
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)