Received: from mailhub7.libertysurf.fr (mailhub7.libertysurf.fr [195.154.209.27]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA93886 for ; Sat, 1 Apr 2000 03:46:13 -0500 (EST) (envelope-from ph.l@libertysurf.fr) Received: from libertysurf.fr (ppp41-orleans1.libertysurf.fr [213.36.24.41]) by mailhub7.libertysurf.fr (8.9.3/8.9.3) with ESMTP id KAA48364 for ; Sat, 1 Apr 2000 10:45:09 +0200 (CEST) Message-ID: <38E5C224.7F88EF62@libertysurf.fr> Date: Sat, 01 Apr 2000 04:32:20 -0500 From: Philippe =?iso-8859-1?Q?Lef=E8vre?= X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14 i586) X-Accept-Language: fr-FR, fr, fr-BE, fr-CH, en-US, en-GB, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Trying to use a foreign key with Postgresql Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/2 I've already sent this request for help to the novice mail list but I never get any answer. So I wonder if it was the good list for that kind of question and I'm publishing it here. (thousand of apologies if I'm wrong !) I'm a beginner with postgres and I'm trying to understand how I can workaround the FOREIGN KEY mechanism within Postgres. I've read that it's not supported yet and we can use a trigger. So I created: CREATE TABLE "tbl_article" ( "i_article" serial PRIMARY KEY, "repere" text, "refmedia" int4 NOT NULL, "theme" int4 NOT NULL, "mot_cle1" int4 NOT NULL, "mot_cle2" int4, "mot_cle3" int4); and: CREATE TABLE "tbl_theme" ( "i_theme" serial PRIMARY KEY, "theme" text NOT NULL UNIQUE); then: CREATE FUNCTION chk_itheme_proc(int4) RETURNS int4 AS 'SELECT COUNT(i_theme) FROM tbl_theme WHERE i_theme = $1;' LANGUAGE 'sql'; and I created a trigger: CREATE TRIGGER chk_itheme_trig BEFORE INSERT OR UPDATE ON tbl_article FOR EACH ROW EXECUTE PROCEDURE chk_itheme_proc ('theme'); but I get the message: ERROR: CreateTrigger: function chk_itheme_proc() does not exist If I do: SELECT theme FROM tbl_article WHERE chk_itheme_proc(5) = 1; -- i_theme goes from 1 to 12 theme ----- 11 (1 row) If I do now: SELECT theme FROM tbl_article WHERE chk_itheme_proc(15) = 1; theme ----- (0 row) So I can see that the function is available .... I'm stuck with this issue and I wasn't able to find any help on documentations. Is there somebody who could help me ??? Many thanks in advance, PhiL