Re: enforcing constraints across multiple tables

Quoth andrew(dot)geery(at)gmail(dot)com (Andrew Geery):
>
> 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))
> );

Instead of this, try

create table person_food (
person_id int not null references person,
food_id int not null,
food_group_id int not null,

foreign key (food_id, food_group_id)
references food (id, food_group_id),
unique (person_id, food_group_id)
);

If you wish to move foods between groups, the foreign key above will
need to be ON UPDATE CASCADE.

Ben