Re: [SQL] Referencial integrity

"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(dot)Elphick(at)lfix(dot)co(dot)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