Re: [SQL] Referencial integrity - Mailing list pgsql-sql

"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