Re: [GENERAL] Foreign key - Mailing list pgsql-general

On Wed, 15 Dec 1999, Patrick Welche wrote:

> How do they work?
> [SNIP]
> Anyone know of a tutorial/give me a hint?

refint works by adding two triggers, one to the parent table and the other
to the child table.  refint does have a bug that stems from saveplan() (
or something similar, i forget the function name ) -- if you update/delete
a cascading fk, then attempt to update/delete ANOTHER cascading fk, refint
will attempt to reuse the first query plan ( ie: delete from employee
where emp_id=2, delete from employee where emp_id=3.  refint uses the plan
from '.. where emp_id=2', which causes it to fail ).

( note that $PGSQL_SRC_ROOT/contrib/spi/refint.example has detailed
documentation. )

create sequence employee_seq;
create table employee
(
   emp_id int4 not null default nextval('employee_seq'),
   emp_name varchar(30) not null,
   primary key (emp_id)
);

create sequence emp_expense_seq;
create table emp_expense
(
   expense_id int4 not null default nextval('emp_expense_seq'),
   emp_id int4 not null,
   descr varchar(100) not null,
   ondate date not null,
   primary key (expense_id)
);

-- parent trigger
create trigger employee_empid_pfk
 before delete or update on employee
 for each row
  execute procedure check_foreign_key( '1', 'cascade', 'emp_id',
       'emp_expense', 'emp_id' );

-- child trigger
create trigger emp_expense_empid_fk
 before insert or update on emp_expense
 for each row
 execute procedure check_primary_key ('emp_id', 'employee', 'emp_id' );

-- data inserts
insert into employee (emp_id,emp_name) values(
 NEXTVAL('employee_seq'), 'Myself');

insert into emp_expense (expense_id,emp_id,descr,ondate) values(
 NEXTVAL('emp_expense_seq'),  currval('employee_seq'), 'Test',
 CURRENT_DATE);

insert into emp_expense (expense_id,emp_id,descr,ondate) values(
 NEXTVAL('emp_expense_seq'), currval('employee_seq'), 'Test #2',
 CURRENT_DATE);

-- selects
caffeine=> select * from employee;
emp_id|emp_name
------+--------
     1|Myself
(1 row)

caffeine=> select * from emp_expense;
expense_id|emp_id|descr  |    ondate
----------+------+-------+----------
         1|     1|Test   |12-16-1999
         2|     1|Test #2|12-16-1999
(2 rows)

-- updates
caffeine=> update employee set emp_id=2;
UPDATE 1
caffeine=> select * from employee;
emp_id|emp_name
------+--------
     2|Myself
(1 row)

caffeine=> select * from emp_expense;
expense_id|emp_id|descr  |    ondate
----------+------+-------+----------
         1|     2|Test   |12-16-1999
         2|     2|Test #2|12-16-1999
(2 rows)

( exit psql session due to previously mentioned bug )

-- deletes
caffeine=> delete from employee where emp_id=2;
DELETE 1
caffeine=> select * from employee;
emp_id|emp_name
------+--------
(0 rows)

caffeine=> select * from emp_expense;
expense_id|emp_id|descr|ondate
----------+------+-----+------
(0 rows)

so it works, besides that one bug.  of course, pgsql 7.0 will have full
foreign key support ( including REFERENCES syntax ).

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
"Tell a man that there are 400 billion stars and he'll believe you.
 Tell him a bench has wet paint and he has to touch it."