Re: [GENERAL] Foreign Key

--- Howie <caffeine@toodarkpark.org> wrote:
> On Wed, 6 Oct 1999, Herouth Maoz wrote:
>
> > At 01:10 +0200 on 06/10/1999, Howie wrote:
> >
> >
> > > for now, refint ( $PGSQL_SRC_ROOT/contrib/spi/ )
> is what one should be
> > > using for foreign keys.  requires two triggers,
> one on the parent and one
> > > on the child.  works nicely.
> >
> > Does it? I was under the impression that it
> supported cascading deletes but
> > not cascading updates.
>
> CREATE SEQUENCE employee_seq START 1 INCREMENT 1;
> CREATE SEQUENCE expense_seq START 1 INCREMENT 1;
>
> CREATE TABLE employee
> (
>    emp_id int4 not null default
> nextval('employee_seq'),
>    emp_name varchar(30) NOT NULL,
>    PRIMARY KEY (emp_id)
> );
>
> CREATE TABLE emp_expense
> (
>    emp_id int4 not null,
>    expense_id int4 not null default
> nextval('expense_seq'),
>    descr varchar(100) NOT NULL,
>    ondate date not null,
>    primary key (expense_id)
> );
>
> CREATE TRIGGER expense_empid_fk
>  BEFORE INSERT OR UPDATE ON emp_expense
>  FOR EACH ROW
>  EXECUTE PROCEDURE check_primary_key('emp_id',
> 'employee', 'emp_id');
>
> CREATE TRIGGER employee_empid_propk
>  AFTER DELETE OR UPDATE ON employee
>  FOR EACH ROW
>  EXECUTE PROCEDURE check_foreign_key( '1',
> 'cascade', 'emp_id',
>    'emp_expense', 'emp_id');
>
> ----
>
> caffeine=> select * from employee;
> emp_id|emp_name
> ------+--------
>      2|Myself
> (1 row)
>
> caffeine=> select * from emp_expense;
> emp_id|expense_id|descr  |    ondate
> ------+----------+-------+----------
>      2|         1|Test   |10-06-1999
>      2|         2|Test #2|10-06-1999
> (2 rows)
>
> caffeine=> update employee set emp_id=5;
> UPDATE 1
> caffeine=> select * from emp_expense;
> emp_id|expense_id|descr  |    ondate
> ------+----------+-------+----------
>      5|         1|Test   |10-06-1999
>      5|         2|Test #2|10-06-1999
> (2 rows)
>
> caffeine=> select version();
> version
>
>
--------------------------------------------------------
> PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by
> egcc
> (1 row)


Now do another update (in the same session):

update employee set emp_id=6;

Followed by a select:

select * from emp_expense;

What's the emp_id value?

In the version of refint.c which was release with
6.5.0, there was an error because after the first
cascading update trigger was executed, the plan was
saved (which includes the value of the foreign key
to be updated), since the cascading update code was
simply the cascading delete code. Saving the SPI
plan for deletes is fine, but for updates it can
cause either (a) the wrong value to updated or
(b) an insertion of new rows.

Perhaps this has been fixed, but I doubt it.

Mike Mascari
(mascarim@yahoo.com)


=====

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com