Re: Correct implementation of 1:n relationship with n>0?

2013/4/30 Anton Gavazuk <antongavazuk(at)gmail(dot)com>

> Hi,
>
> Can you explain what you are trying to achieve because it's not clear...
>
> There are 2 types of relationships which might be used in your case:
>
> 1) unidirectional relationship from list_item to list through foreign
> key on list
> 2) bidirectional relationship implemented through join table which
> contains references between both tables
> These are pretty standard generic techniques applied many times and
> don't require any "programming"
>
> Thanks,
> Anton
>
> On Apr 30, 2013, at 16:39, Wolfgang Keller <feliphil(at)gmx(dot)net> 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.
> >
> > TIA,
> >
> > Sincerely,
> >
> > Wolfgang
> >
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
>
"
2) bidirectional relationship implemented through join table which
contains references between both tables
"

What is an example of that?