Re: [GENERAL] Re: referential integrity (fwd)

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