Re: enforcing constraints across multiple tables - Mailing list pgsql-sql

Quoth andrew.geery@gmail.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_idint 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