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

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of Wolfgang Keller
> Sent: Tuesday, April 30, 2013 2:19 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Correct implementation of 1:n relationship with n>0?
>
> > 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).
>
> <duck>
>
> BTW: If every list_item could be part of any number (>0) of lists, you
> get a n:m relationship with a join table and then the issue that each
> list_item has to belong to at least one list arises as well.
>
> Maybe there should also be a standard solution documented somewhere for
> this case, too.
>
> </duck>
>
> Sincerely,
>
> Wolfgang
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Wolfgang,

If it is n:m relationship, than appearance of the LIST in list_item table :

(list_id int not null,
item_id int not null,
constraint PK_list_item Primary Key (list_id, item_id),
constraint FK_ItemList_List Foreign Key (list_id) references List (list_id) on delete cascaded on update restrict,
constraint FK_ItemList_Item Foreign Key (item_id) references Item (item_id) on delete cascaded on update restrict)

means that this LIST has at least one ITEM assigned to it.
Same goes for the ITEM: if it is assigned to at least one List it should appear in this "cross table".

It is application responsibility to populate this table, when Items assigned to Lists.
It is database responsibility (through declarative foreign keys) to make sure that Lists and Items used in "cross table" have corresponding records in "parent" tables.
Using triggers (which is SQL extension implemented differently in every DBMS) database also can support such feature, as: "when last Item removed from the List - drop now "empty" List. Which I don't consider a good idea - what if you'll need this list in the future? Why re-create it?

As for your original problem with 1:n relationship, n should be starting from 0 for the case when new List is created and there is no Items to assign to this new List, yet. In this case, FK on Items table referencing List table makes sure that every Item references existing (valid) List.

Regards,
Igor Neyman