Date: Fri, 3 Jan 2003 07:57:45 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Pedro Igor <pedroigor@aip.com.br>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Query
In-Reply-To: <000d01c2b32e$8529eca0$0b00010a@aip.com.br>
Message-ID: <20030103075235.Q3488-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Fri, 3 Jan 2003, Pedro Igor wrote:

> Regards .....
>
>   I hope someone can help me in this query.
>   Have a field in a table that needs to check if another table has the value that is being inserted.
>   Ex:
>     table A
>       - id int constraint pkey_id primary key,
>
>
>     table B
>       - id int constraint fkey_A_B references A,
>
>     Here comes my doubt:
>
>     table C
>       - id int constraint fkey_A_C references A check (if exists B.id
> = C.id)
>
>     How can i build this expression so, when I insert a tupple in
> table C the field will check in the table A(ok, because is a foreign
> key) and also in table B ....

If you only want insert(/update) time checks (ie that deleting from B
doesn't violate) then a trigger should work.  I think a check constraint
to a function that does the work will work as well.  Subsellects in check
constraints are a nasty problem that hasn't been solved yet (since those
do imply the inverse constraint).

Of course if you want two foreign keys you should be able to do that as
well.