From: Ben Morrow <ben@morrow.me.uk>
To: andrew.geery@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: enforcing constraints across multiple tables
Date: Sat, 26 Jun 2010 02:19:09 +0100
Message-ID: <20100626011908.GA45211@osiris.mauzo.dyndns.org> (raw)
In-Reply-To: <AANLkTilvEsBFcRoNvgLi_2h-axAZuSrgb-IlL6tuIk4t@mail.gmail.com>
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_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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, andrew.geery@gmail.com
Subject: Re: enforcing constraints across multiple tables
In-Reply-To: <20100626011908.GA45211@osiris.mauzo.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox