Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA82383 for ; Mon, 15 Nov 1999 17:56:18 -0500 (EST) (envelope-from peter@peter-e.yi.org) Received: from regulus.its.uu.se ([130.238.7.19]:62669 "EHLO peter-e.yi.org") by merganser.its.uu.se with ESMTP id ; Mon, 15 Nov 1999 23:56:00 +0100 Received: from peter (helo=localhost) by peter-e.yi.org with local-esmtp (Exim 3.02 #2) id 11n4dx-0000G1-00; Sun, 14 Nov 1999 19:44:53 +0100 Date: Sun, 14 Nov 1999 19:44:52 +0100 (CET) From: Peter Eisentraut To: juman cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Verificate values in other table? In-Reply-To: <000b01bf2e8a$943f3c80$64010a0a@vodka.booze.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: Peter Eisentraut 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 INSTEAD NOTHING; 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 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