Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA18250 for ; Thu, 13 Jan 2000 16:22:11 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@max01-088.enterprise.net [194.72.195.88]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id VAA03357; Thu, 13 Jan 2000 21:22:08 GMT Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id VAA23887; Thu, 13 Jan 2000 21:21:16 GMT Message-Id: <200001132121.VAA23887@linda.lfix.co.uk> X-Authentication-Warning: linda.lfix.co.uk: Host olly@localhost [127.0.0.1] claimed to be lfix.co.uk X-Mailer: exmh version 2.1.1 10/15/1999 (debian) To: "Pausas Fuentes, Jaume" cc: pgsql-sql@postgreSQL.org, olly@linda.lfix.co.uk Subject: Re: [SQL] foreign key implementation In-Reply-To: Message from "Pausas Fuentes, Jaume" of "Thu, 13 Jan 2000 11:14:24 GMT." <84f54420.442084f5@alehop.com> References: <84f54420.442084f5@alehop.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 13 Jan 2000 21:21:16 +0000 From: "Oliver Elphick" "Pausas Fuentes, Jaume" wrote: >Hi > >Anybody have an implementation of the sql foreign key? > >Reading the manual it says that I must use the create trigger command >but how to implement a procedure to do what foreing key does? You need the refint package out of contrib. Then use commands like these: create database bray; \connect bray -- refint functions (from postgresql-contrib package). These are -- needed until foreign keys are implemented in PostgreSQL \i contrib/spi/refint.sql create table area ( id char(2) primary key, name text not null, vat_class char(1) default 'S' ); create trigger area_fref before DELETE or UPDATE on area for each row execute procedure check_foreign_key (1, 'restrict', 'id', 'customer', 'area' ); create table customer ( acs_code char(8), ... area char(2) references area (id), ... ); create trigger customer_pkref2 before INSERT or UPDATE on customer for each row execute procedure check_primary_key ('area', 'area', 'id'); -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "For the LORD is good; his mercy is everlasting; and his truth endureth to all generations." Psalms 100:5