Re: enforcing constraints across multiple tables
От | Ben Morrow |
---|---|
Тема | Re: enforcing constraints across multiple tables |
Дата | |
Msg-id | 20100626011908.GA45211@osiris.mauzo.dyndns.org обсуждение исходный текст |
Ответ на | enforcing constraints across multiple tables (Andrew Geery <andrew.geery@gmail.com>) |
Список | 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
В списке pgsql-sql по дате отправления: