enforcing constraints across multiple tables - Mailing list pgsql-sql
| From | Andrew Geery |
|---|---|
| Subject | enforcing constraints across multiple tables |
| Date | |
| Msg-id | AANLkTilvEsBFcRoNvgLi_2h-axAZuSrgb-IlL6tuIk4t@mail.gmail.com Whole thread Raw |
| Responses |
Re: enforcing constraints across multiple tables
(Ben Morrow <ben@morrow.me.uk>)
|
| List | 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 $$
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
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