pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Elphick <olly@lfix.co.uk>
To: "Jos " "Hern ndez" Zavala <joseghz@yahoo.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Referencial integrity
Date: Tue, 26 Jan 1999 22:38:24 +0000
Message-ID: <199901262238.WAA17374@linda.lfix.co.uk> (raw)
In-Reply-To: <joseghz@yahoo.com> <19990126212726.26932.rocketmail@send205.yahoomail.com>

"Jos " "Hern ndez" Zavala wrote:
  >Hello list.
  >
  >I'm new using postgresql RDBMS a I want to know how 
  >implemet the referencial integrity (primary keys, foreign 
  >keys, etc). I have postgres version 6.3.2

6.3.2 and 6.4.2 parse foreign key syntax, but do not act on the
foreign key constraints.  You need to install the spi module from
contrib and run refint.sql to install the functions in your database.
You must then define triggers to implement the foreign key checks.
There is some documentation for this in contrib/spi.

Here is an example.  There are two tables, country and currency, and a
third, country_ccy which shows which countries use which currencies.
Other tables also refer to the first two, but I have not shown the whole
structure.

country_ccy contains SQL foreign key constraints, but these are not yet
operational, so triggers are also defined. (These will be dropped when
foreign key constraints are made operational.)

create table country
(
	id		char(2)		primary key
					check (id ~ '[A-Z]{2}'),
	name		text		not null,
	region		text,
	telcode		text
)
;

   -- foreign key triggers
   create trigger country_fref
	before DELETE or UPDATE on country
	for each row execute procedure
	check_foreign_key(4, 'restrict', 'id',
		'address', 'country',
		'europe', 'id',
		'country_ccy', 'country',
		'customer', 'country'
		);
    -- (this table is also referenced by tables address, europe and customer,
    --  not shown here)


create table currency
(
	symbol		char(3)		primary key,
	isonum		int2		,
	name		text		not null,
	place_before	bool		not null
					default 't',
	decimals	int		not null
					default 2
					check (decimals = 0 or decimals = 2)
)
;

   -- foreign key triggers
   create trigger currency_fkey
	before DELETE or UPDATE on currency
	for each row execute procedure
	check_foreign_key(3, 'restrict', 'symbol',
		'country_ccy', 'ccy',
		'price', 'currency',
		'stock_allocation', 'ccy'
		);
    -- (this table is also referenced by tables price and stock_allocation,
    --  not shown here)


create table country_ccy
(
	country		char(2)		references country (id),
	ccy		char(3)		references currency (symbol),
	primary key (country, ccy)
)
;

   -- foreign key triggers
   create trigger country_ccy_pkref1
	before INSERT or UPDATE on country_ccy
	for each row execute procedure
	check_primary_key('country', 'country', 'id');

   create trigger country_ccy_pkref2
	before INSERT or UPDATE on country_ccy
	for each row execute procedure
	check_primary_key('ccy', 'currency', 'symbol');



-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Wash me thoroughly from mine iniquity, and cleanse me 
      from my sin. For I acknowledge my transgressions; and 
      my sin is ever before me. Against thee, thee only, 
      have I sinned, and done this evil in thy sight..."
                                   Psalms 51:2-4 





Message-ID: <199901262238.WAA17374@linda.lfix.co.uk>
Permalink:  ../199901262238.WAA17374@linda.lfix.co.uk/
Also on:    postgresql.org/message-id/199901262238.WAA17374@linda.lfix.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: olly@lfix.co.uk, joseghz@yahoo.com
  Subject: Re: [SQL] Referencial integrity
  In-Reply-To: <199901262238.WAA17374@linda.lfix.co.uk>
  References: <199901262238.WAA17374@linda.lfix.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox