Thread: Verificate values in other table?

I'm working on a little database and got stuck with the following problem. I
have two tables that look like this

table1 ( ids char(4) )
table2 ( id char(4), comment text)

What I want to do is that if someone adds or updates a row in in table2 I
want to verify that the <id> given exists in table1. And if it doesn't don't
accept the modification and give the user a error message. Is there some way
to do this?

/ Fredrik

"juman" <juman@spray.se> writes:
> What I want to do is that if someone adds or updates a row in in table2 I
> want to verify that the <id> given exists in table1.

You can do this with a trigger --- I'd suggest writing it in plpgsql.
No canned example at hand, sorry, but there are examples of triggers
applying error checks in the plpgsql section of the manual.
        regards, tom lane

Btw., the term is "Referential integrity".

At some point there was something of that nature in the contrib dir but I
can't seem to find it now.

The usual way to solve this is to make a "rule" like so:

CREATE RULE check_for_junk AS ON insert TO table2 WHERE 0 = (SELECT COUNT(*) FROM table1 WHERE table1.ids = new.id) DO
INSTEADNOTHING;
 

and a similar one for updates. Perhaps you also want one "ON delete TO
table1" to check for the reverse condition.

Proper foreign keys are slated to appear in 7.0.


On 1999-11-14, juman mentioned:

> I'm working on a little database and got stuck with the following problem. I
> have two tables that look like this
> 
> table1 ( ids char(4) )
> table2 ( id char(4), comment text)
> 
> What I want to do is that if someone adds or updates a row in in table2 I
> want to verify that the <id> given exists in table1. And if it doesn't don't
> accept the modification and give the user a error message. Is there some way
> to do this?
> 
> / Fredrik
> 
> 
> ************
> 
> 

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut wrote:

> Proper foreign keys are slated to appear in 7.0.
>

    I still hope so. I've already implemented the "deferred queue
    handling" in the trigger manager and placed the beginning  of
    the  generic  triggers  in  utils/adt/  some  weeks  ago (and
    dropped a note here), but none of the guy's who offered  help
    picked it up so far.

    So again: Who's willing to contribute to it?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #