2013/4/30 Anton Gavazuk <antongava...@gmail.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 <felip...@gmx.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@postgresql.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?
