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

2013/4/30 Wolfgang Keller <feliphil(at)gmx(dot)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....