Re: Approved

Please see the attached file.
>From pgsql-general-owner(at)postgresql(dot)org Fri Jun 6 21:09:37 2003
X-Original-To: pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (developer.postgresql.org [64.117.224.193])
by svr1.postgresql.org (Postfix) with ESMTP id F3C67EFF70
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>; Fri, 6 Jun 2003 05:59:05 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024)
with ESMTP id 20047-04
for <pgsql-general-postgresql(dot)org(at)localhost(dot)postgresql(dot)org>;
Fri, 6 Jun 2003 05:59:04 +0000 (GMT)
Received: from mwinf0201.wanadoo.fr (smtp7.wanadoo.fr [193.252.22.29])
by svr1.postgresql.org (Postfix) with ESMTP id 0827FEFEB0
for <pgsql-general(at)postgresql(dot)org>; Fri, 6 Jun 2003 05:59:04 +0000 (GMT)
Received: from ANice-103-1-6-34.w80-14.abo.wanadoo.fr (unknown [80.14.189.34])
by mwinf0201.wanadoo.fr (SMTP Server) with ESMTP
id 8C00630003B7; Fri, 6 Jun 2003 07:59:03 +0200 (CEST)
Subject: Re: check constraint
From: erwan ancel <erwan(dot)ancel(at)free(dot)fr>
To: Jan Wieck <JanWieck(at)Yahoo(dot)com>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>,
PostgreSQL-general <pgsql-general(at)postgresql(dot)org>
In-Reply-To: <3EDFB49C(dot)7010905(at)Yahoo(dot)com>
References: <1054543920(dot)5523(dot)8(dot)camel(at)brisedorient>
<20030602130819(dot)GA15591(at)wolff(dot)to> <1054562442(dot)6379(dot)12(dot)camel(at)brisedorient>
<3EDFB49C(dot)7010905(at)Yahoo(dot)com>
Content-Type: text/plain; charset=ISO-8859-15
Organization:
Message-Id: <1054879158(dot)3248(dot)2(dot)camel(at)brisedorient>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.4-1.1mdk
Date: 06 Jun 2003 07:59:19 +0200
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200306/155
X-Sequence-Number: 43085

Ok, thanks.
I just did it with 3 triggers. And it seems to work well. I wanted to
avoid this, but it seems to be the best solution.

Le jeu 05/06/2003 à 23:22, Jan Wieck a écrit :
> erwan ancel wrote:
> > well, no... these are not direct foreign keys. The constraint here is
> > that for a given record of D, B pointed by A pointed by the given D must
> > be the same as B pointed by C pointed by the given D.
> >
> > This is not a foreign key, or foreign keys are much more than what I
> > thought.
>
> Looks like a foreign key around the corner to me. Regular referential
> integrity does not allow you to define that, unless you include the B
> keys referenced in A and C into separate fields in D and build multi
> column foreign keys (probably with ON UPDATE CASCADE).
>
> The problem arising from that is that you need to know the values ahead
> or do a lookup and put them into D in a BEFORE trigger.
>
> Also I see problems arising if you later want to update A and/or C. I
> haven't thought it through completely, maybe defining the constraints
> deferred can help you out of that.
>
>
> Jan
>
>
>
> > Erwan
> >
> > Le lun 02/06/2003 à 15:08, Bruno Wolff III a écrit :
> >> On Mon, Jun 02, 2003 at 10:52:00 +0200,
> >> erwan ancel <erwan(dot)ancel(at)free(dot)fr> wrote:
> >> > Hi,
> >> > I would like to know if it is possible to set "complex" constraints on
> >> > databases such as:
> >> >
> >> > A->B means that in table A, each record references a record of table B
> >> > (or NULL)
> >> >
> >> > so we have:
> >> >
> >> > A->B
> >> > C->B
> >> > D->C
> >> > D->A
> >> > constraint: for one record of D, D->A->B = D->C->B
> >> >
> >> > Hope it is clear enough.
> >>
> >> It looks like you are talking about foreign keys. Postgres has foreign key
> >> constraints. You can look at the create table documentation to see how
> >> to define them when creating a table.
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
>
>