2013/4/30 Wolfgang Keller <felip...@gmx.net> > 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 >
I don't think there is the way to achieve that without programming (less important in which language...) Your rules say: 1) End user - can't be able to create new list at all... (just new List) (If he can create new list - it will brake the your rule 2) He always creates "list_item" - but in one case - should pick existing "list" in another he must enter info about new list_item together with info about new list.... Technically - create new list_item calls one or another function 2) End User - just can delete list_item (function will make additional check - if there is no more list_items in my list - delete the list as well - the same check will be run after "repoint") Everything else - will be assured with existing FK integrity....
