Re: *Proper* solution for 1..* relationship?

Wolfgang Keller <feliphil(at)gmx(dot)net> wrote:

> It hit me today that a 1..* relationship can't be implemented just by a
> single foreign key constraint. I must have been sleeping very deeply not
> to notice this.
>
> E.g. 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 "proper" solution would require:
>
> 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. Triggers, stored procedures...?
>
> (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?

The most straightforward way I know to enforce this is to check
that at least one child exists in a DEFERRED trigger on the the
parent.  You still need to worry about concurrency issues.  One way
to do that is to use only SERIALIZABLE transactions.  There are
other ways, though they take more to describe and to implement.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company