Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37271=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1UXCE1-0006gy-5R
	for pgsql-sql@arkaria.postgresql.org; Tue, 30 Apr 2013 15:12:13 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-sql-owner+M37271=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
	id 1UXCE0-0002b7-LN
	for pgsql-sql@arkaria.postgresql.org; Tue, 30 Apr 2013 15:12:12 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <achill@matrix.gatewaynet.com>) id 1UXCDz-0002b2-VP
	for pgsql-sql@postgresql.org; Tue, 30 Apr 2013 15:12:12 +0000
Received: from adsltrust.ath.forthnet.gr ([194.219.204.174]
	helo=smadev.internal.net)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <achill@matrix.gatewaynet.com>) id 1UXCDw-0002nO-Oq
	for pgsql-sql@postgresql.org; Tue, 30 Apr 2013 15:12:11 +0000
X-Bogosity: No, tests=bogofilter
Received: from smadev.internal.net (localhost [127.0.0.1])
	by smadev.internal.net (8.14.5/8.14.5) with ESMTP id r3UFC5QD015394
	for <pgsql-sql@postgresql.org>; Tue, 30 Apr 2013 18:12:05 +0300 (EEST)
	(envelope-from achill@matrix.gatewaynet.com)
Received: (from achill@localhost)
	by smadev.internal.net (8.14.5/8.14.5/Submit) id r3UFC5GJ015393
	for pgsql-sql@postgresql.org; Tue, 30 Apr 2013 18:12:05 +0300 (EEST)
	(envelope-from achill@matrix.gatewaynet.com)
X-Authentication-Warning: smadev.internal.net: achill set sender to
	achill@matrix.gatewaynet.com using -f
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>
Organization: Dynacom Tankers Mgmt
X-Face: "g.Z.Lx$T1ZMcQ%hC!e^E&tD,cT:"bTs45WM(,vUj@8QBz6}T'sn+EnZTzy`UVQ:&A=`_;
	f)V+K4z}rG5:(uu[b:WY'*`6F"ou-Or(q; u{#Gxx|MkO4E.vh@E}[#7Ytt"shtU>A&@CO`
	a|Wx]m_wRD,?4!'Ir1$4iis{/.WU<`#dhKI]g2w^!B[CvRJr+W|; -VS~QcL!s1"'??rct}
	^=5Fa!W!{a}Jd:W%6,E[N\r-<)T'_N[~3fy9pF"b>-Yj^p}/2tPudP>I"$%w]"W4CIja6J
	Tajm}"8t`-hJlf2kRQ_V,eT_kN6KLG+~2mZ+cPX,p,xQN9QVR<BSn
User-Agent: KMail/4.8.4 (FreeBSD/8.3-RELEASE; KDE/4.8.4; amd64; ; )
In-Reply-To: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
References: <20130430163905.77d43412c0176c3d9ebd8d90@gmx.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="US-ASCII"
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On =D4=F1=E9 30 =C1=F0=F1 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.
>=20
> 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).
>=20
> A "correct" solution would require (at least?):
>=20
> 1. A foreign key pointing from each list_item to its list
>=20
> 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.
>=20
> 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?
>=20
> (4. Anything else that I've not seen?)
>=20
> Is there a "straight" (and tested) solution for this in PostgreSQL, that
> someone has already implemented and that can be re-used?
>=20
> 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.
>=20

I think your best bet is a trigger.
use RAISE EXCEPTION to indicate an erroneous situation so as to=20
make the transaction abort. (there is nothing wrong in getting your hands=
=20
dirty with pl/pgsql btw)

> TIA,
>=20
> Sincerely,
>=20
> Wolfgang
>=20
>=20
>=20
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql