Re: check constraint

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.ancel@free.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@postgresql.org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html



--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #