Thread: Re: referential integrity (fwd)

Sir:

  I have two tables  customer and order defined as follows:

  create table customer (
      custno   integer not null,
      name     varchar(30),
      address  varchar(30),
      primary key (custno));

  create table order (
      orderno  integer not null,
      custno   integer,
      orddate  date,
      amount   numeric(7,2),
      primary key (orderno));

  create sequence 'next_cust' start 1;
  create sequence 'next_ord'  start 1;


  My problem is how would i implement referential integrity so that
whenever i insert a new order, it should check first if the custno exist
in the customber table. Can somebody please show me an example how would i
do it in this case?

  Thank you...


                              Manny C. Cabido
                              ====================================
                              e-mail:manny@tinago.msuiit.edu.ph
                                     manny@sun.msuiit.edu.ph
                              =====================================

Manuel Cabido wrote:

>
>   My problem is how would i implement referential integrity so that
> whenever i insert a new order, it should check first if the custno exist
> in the customber table. Can somebody please show me an example how would i
> do it in this case?

You may do this via triggers and procedures using the semi-built-in
postgresql language called "PL/pgSQL".  Here are a couple of useful
references:

    http://www.deja.com/getdoc.xp?AN=548176178  (to activate the language in
your db)
    http://www.postgresql.org/docs/postgres/sql-createtrigger.htm
    http://www.postgresql.org/docs/postgres/sql-createfunction.htm

There are numerous other examples in the .../src/test/regress/sql/plpgsql.sql
file.  Yours will look something like this:

CREATE FUNCTION check_for_customer() RETURNS OPAQUE AS
'DECLARE
        cust RECORD;
 BEGIN
        SELECT INTO temp * FROM customer WHERE custno = NEW.custno;
        if not found then
                RAISE EXCEPTION ''TRIGGER % % % % on RELATION % : Cannot
insert an order with a non-existent customer number (%)'', TG_NAME, TG_WHEN,
TG_LEVEL, TG_OP, TG_RELNAME, NEW.custno;
        end if;
        RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER order_insert_trigger
        AFTER INSERT ON order
        FOR EACH ROW EXECUTE PROCEDURE check_for_customer ();

There is work in progress to provide more automated support for this in
version 7.x, possibly sometime this year.

Cheers,
Ed Loehr