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