From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Correct implementation of 1:n relationship with n>0?
Date: Tue, 30 Apr 2013 18:12:05 +0300
Message-ID: <3724312.iEkiItIUeG@smadev.internal.net> (raw)
In-Reply-To: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
References: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On Ôñé 30 Áðñ 2013 16:39:05 Wolfgang Keller 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.
>
I think your best bet is a trigger.
use RAISE EXCEPTION to indicate an erroneous situation so as to
make the transaction abort. (there is nothing wrong in getting your hands
dirty with pl/pgsql btw)
> TIA,
>
> Sincerely,
>
> Wolfgang
>
>
>
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
--
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: achill@matrix.gatewaynet.com
Subject: Re: Correct implementation of 1:n relationship with n>0?
In-Reply-To: <3724312.iEkiItIUeG@smadev.internal.net>
* 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