Thread: Foreign key

How do they work?

Date: Wed, 6 Oct 1999 15:25:27 +0000 (GMT)
Subject: Re: [GENERAL] Foreign Key
Message-ID: <Pine.LNX.3.96.991006152248.8877H-100000@rabies.toodarkpark.org>

I had a look at the above message which involved the refint contrib code. I
thought the equivalent might be:

CREATE TABLE employee
(
   emp_id serial primary key,
   emp_name varchar(30) NOT NULL
);

CREATE TABLE emp_expense
(
   expense_id serial primary key,
   emp_id int4 references employee match full on update cascade,
   descr varchar(100) NOT NULL,
   ondate date not null
);

insert into employee values (2,'Myself');
insert into emp_expense values (1,2,'Test','10-06-1999');
insert into emp_expense values (2,2,'Test #2','10-06-1999');
select * from employee;
select * from emp_expense;
update employee set emp_id=5;
select * from emp_expense;     -- hope emp_id magically changes to 5

but obviously it can't be, as if I update employee, there is nothing in table
employee to say "take a look at emp_expense and update emp_id over there".
There only is something in emp_expense to say "check employee to see that
emp_id here is valid".

Anyone know of a tutorial/give me a hint?

Cheers,

Patrick

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."

Just tried what I wrote ages ago below, and it now magically does work!
(Don't know since when)

Thanks Jan and co,

Patrick

On Wed, Dec 15, 1999 at 10:56:10PM +0000, Patrick Welche wrote:
> How do they work?
>
> Date: Wed, 6 Oct 1999 15:25:27 +0000 (GMT)
> Subject: Re: [GENERAL] Foreign Key
> Message-ID: <Pine.LNX.3.96.991006152248.8877H-100000@rabies.toodarkpark.org>
>
> I had a look at the above message which involved the refint contrib code. I
> thought the equivalent might be:
>
> CREATE TABLE employee
> (
>    emp_id serial primary key,
>    emp_name varchar(30) NOT NULL
> );
>
> CREATE TABLE emp_expense
> (
>    expense_id serial primary key,
>    emp_id int4 references employee match full on update cascade,
>    descr varchar(100) NOT NULL,
>    ondate date not null
> );
>
> insert into employee values (2,'Myself');
> insert into emp_expense values (1,2,'Test','10-06-1999');
> insert into emp_expense values (2,2,'Test #2','10-06-1999');
> select * from employee;
> select * from emp_expense;
> update employee set emp_id=5;
> select * from emp_expense;     -- hope emp_id magically changes to 5
>
> but obviously it can't be, as if I update employee, there is nothing in table
> employee to say "take a look at emp_expense and update emp_id over there".
> There only is something in emp_expense to say "check employee to see that
> emp_id here is valid".
>
> Anyone know of a tutorial/give me a hint?
>
> Cheers,
>
> Patrick
>
> ************
>