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

If you want to enforce a 1 to N relationship, You can use a CONSTRAINT
TRIGGER on the List table to ensure that there is at least one list item -
if there isn't then raise an exception 'No List Items!!!'. You'd want to
define the constraint as INITIALLY DEFERRED.
http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html
http://www.postgresql.org/docs/9.2/interactive/sql-set-constraints.html

A constraint trigger is the closest feature to an ASSERTION statement
defined by the SQL standard.

You'd begin a transaction, Insert to List and Listitem. Then commit. If
you try this any other way, your constraint trigger will fire off an
exception and will force a rollback of the initial insert.

IHTH.

On Fri, Apr 26, 2013 at 4:59 AM, 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?
>
> TIA,
>
> Sincerely,
>
> Wolfgang
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

--
Regards,
Richard Broersma Jr.