enforcing constraints across multiple tables

From: Andrew Geery <andrew(dot)geery(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: enforcing constraints across multiple tables
Date: 2010-06-25 17:52:40
Message-ID: AANLkTilvEsBFcRoNvgLi_2h-axAZuSrgb-IlL6tuIk4t@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a question about checking a constraint that is spread across multiple
(in the example below, two) tables. In the example below, every food (food
table) is in a food group (food_group table). For every person (person
table), I want to enforce the constraint that there can only be one food in
a given food group (person_food link table) [think of it as every person may
have a favorite food in a given food group].

The problem seems to be that the link is in the person_food table, but the
information that is needed to verify the constraint is also in the food
table (i.e., what food group is the food in?).

There are two problems here:
(1) don't allow a food to be associated with a person if there is already a
food in the same food group associated with the person; and
(2) don't allow the food group for a food to be changed if this would
violate (1)

To enforce (1), I created a function to check whether a given food can be
associated with a given person (is there already a food in the same food
group associated with the person?) and added a check constraint to the
person_food table.
To enforce (2), I wasn't able to use a check constraint because the
constraint was being checked with the existing data, not with the new data.
I had to add an after trigger that called a function to do the check.

My questions are:
(A) Is there a way to check (2) above using a constraint and not a trigger?
(B) Is there an easier way to solve this problem? Does the complicated
nature of the solution make the design poor?
(C) Should I not worry about this constraint at the DB level and just
enforce it at the application level?

Below are the tables, functions and triggers I was using.

Thanks!
Andrew

===========================================================================

create table person (
id serial primary key,
name varchar not null
);

create table food_group (
id serial primary key,
name varchar not null
);

create table food (
id serial primary key,
food_group_id int not null references food_group,
name varchar not null
);

create table person_food (
person_id int not null references person,
food_id int not null references food,
primary key (person_id, food_id),
check (is_person_food_unique(person_id, food_id))
);

create or replace function is_food_person_unique(_food_id int) returns
boolean as $$
declare cnt int;
begin
raise notice 'food_id: %', _food_id;
select count(*) into cnt from
(select
pf.person_id
from
person_food pf
join food f on pf.food_id = f.id
join (select fg.* from food_group fg left join food f on fg.id =
f.food_group_id where f.id = _food_id) food_food_group
on f.food_group_id = food_food_group.id
group by
pf.person_id
having
count(*) > 1) s;
return cnt = 0;
end;
$$ language plpgsql;

create or replace function is_person_food_unique(_person_id int, _food_id
int) returns boolean as $$
declare cnt int;
begin
select
count(*) into cnt
from
person_food pf
join food f on pf.food_id = f.id
join (select distinct fg.* from food_group fg left join food f
on fg.id = f.food_group_id where f.id = _food_id) food_food_group
on f.food_group_id = food_food_group.id
where
pf.person_id = _person_id;
return cnt = 0;
end;
$$ language plpgsql;

create or replace function check_food_trigger() RETURNS TRIGGER AS $$
begin
if not is_food_person_unique(NEW.id) then
raise exception 'error changing the food group to %',
NEW.food_group_id;
end if;
return new;
end;
$$ language plpgsql;

create trigger after_food after update on food
for each row execute procedure check_food_trigger();

alter table person_food add constraint check_person_food_unique
check(is_person_food_unique(person_id, food_id));

insert into person values (1, 'Me');
insert into food_group values (1, 'Fruit');
insert into food_group values (2, 'Meat');
insert into food values (1, 1, 'Apple');
insert into food values (2, 1, 'Orange');
insert into food values (3, 2, 'Chicken');
insert into person_food values (1, 1);
insert into person_food values (1, 3);
insert into person_food values (1, 2); -- correctly fails

update food set food_group_id = 1 where id = 3; -- correctly fails

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Adrian Klaver 2010-06-25 18:37:25 Re: COPY command and required file permissions
Previous Message Lee Hachadoorian 2010-06-25 16:34:01 Re: Average of Array?