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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."