Message-ID: <517FF35E.7090603@dalibo.com>
Date: Tue, 30 Apr 2013 18:37:50 +0200
From: Vik Fearing <vik.fearing@dalibo.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130329 Thunderbird/17.0.5
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, Wolfgang Keller <feliphil@gmx.net>
Subject: Re: Correct implementation of 1:n relationship with n>0?
References: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
In-Reply-To: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 update cascade 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.lists where 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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql