Re: [GENERAL] Foreign key

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(at)toodarkpark(dot)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."