Re: Correct implementation of 1:n relationship with n>0? - Mailing list pgsql-sql
From | Vik Fearing |
---|---|
Subject | Re: Correct implementation of 1:n relationship with n>0? |
Date | |
Msg-id | 517FF35E.7090603@dalibo.com Whole thread Raw |
In response to | Correct implementation of 1:n relationship with n>0? (Wolfgang Keller <feliphil@gmx.net>) |
List | pgsql-sql |
On 04/30/2013 04:39 PM, Wolfgang Keller wrote: > It hit me today that a 1:n relationship can't be implemented just by a > single foreign key constraint if n>0. I must have been sleeping very > deeply not to notice this. > > E.g. if there is a table "list" and another table "list_item" and the > relationship can be described as "every list has at least one > list_item" (and every list_item can only be part of one list, but > this is trivial). > > A "correct" solution would require (at least?): > > 1. A foreign key pointing from each list_item to its list > > 2. Another foreign key pointing from each list to one of its list_item. > But this must be a list_item that itself points to the same list, so > just a simple foreign key constraint doesn't do it. > > 3. When a list has more than one list_item, and you want to delete the > list_item that its list points to, you have to "re-point" the foreign > key constraint on the list first. Do I need to use stored proceures > then for all insert, update, delete actions? > > (4. Anything else that I've not seen?) > > Is there a "straight" (and tested) solution for this in PostgreSQL, that > someone has already implemented and that can be re-used? > > No, I definitely don't want to get into programming PL/PgSQL myself. > especially if the solution has to warrant data integrity under all > circumstances. Such as concurrent update, insert, delete etc. Does a solution like the following work for you? It has some plpgsql, but you didn't do it yourself. drop schema if exists one_n cascade; create schema one_n; set search_path to 'one_n'; create table lists (id serial primary key, name text); create table list_items ( id serial primary key, list_id integer not null references lists (id) on updatecascade on delete cascade, value text); create index on list_items (list_id); create or replace function list_cardinality_enforcer() returns trigger as $$ declare __list_id integer; begin if tg_table_name = 'lists' then __list_id := new.id; elsif tg_table_name = 'list_items' then __list_id:= old.list_id; else raise exception 'This trigger function is only intended for tables lists and list_items'; end if; /* Take a lock on the list id to handle concurrency */ perform id from one_n.listswhere id = __list_id for update; if not exists (select 1 from one_n.list_items li where list_id = __list_id) then raise exception 'List with id=% does not have any items', __list_id; end if; return null; end; $$ language plpgsql; create constraint trigger list_cardinality_enforcer after insert on lists deferrable initially deferred for each row execute procedure list_cardinality_enforcer(); create constraint trigger list_cardinality_enforcer after update or delete on list_items deferrable initially deferred for each row execute procedure list_cardinality_enforcer(); My basic tests work, it's possible I've not thought of something.