Re: [GENERAL] Foreign Key

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)

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."