From: Anton Gavazuk <antongavazuk@gmail.com>
To: Wolfgang Keller <feliphil@gmx.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Correct implementation of 1:n relationship with n>0?
Date: Tue, 30 Apr 2013 17:14:32 +0200
Message-ID: <8817341295868562647@unknownmsgid> (raw)
In-Reply-To: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
References: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Hi,
Can you explain what you are trying to achieve because it's not clear...
There are 2 types of relationships which might be used in your case:
1) unidirectional relationship from list_item to list through foreign
key on list
2) bidirectional relationship implemented through join table which
contains references between both tables
These are pretty standard generic techniques applied many times and
don't require any "programming"
Thanks,
Anton
On Apr 30, 2013, at 16:39, Wolfgang Keller <feliphil@gmx.net> wrote:
> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: antongavazuk@gmail.com, feliphil@gmx.net
Subject: Re: Correct implementation of 1:n relationship with n>0?
In-Reply-To: <8817341295868562647@unknownmsgid>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox