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 <[EMAIL PROTECTED]> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."
************
