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
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