[ADMIN] Problems with cascading update and check_primary_key

[Brad Saxton]

I am trying to get referential integrity working with cascading deletes
and updates. The deletes work fine but updates seem to have a problem. For
example,

CREATE TABLE "master" (
        id      int
);
CREATE

CREATE TRIGGER "master_refcheck"
        BEFORE DELETE OR UPDATE ON "master" FOR EACH ROW
        EXECUTE PROCEDURE
                check_foreign_key (1, 'cascade', 'id',
                        'slave', 'master_id');
CREATE

CREATE TABLE "slave" (
        master_id       int
);
CREATE

CREATE TRIGGER "slave_primary_check"
        BEFORE INSERT OR UPDATE ON "slave" FOR EACH ROW
        EXECUTE PROCEDURE "check_primary_key" ('master_id', 'master',
'id');

INSERT INTO MASTER VALUES (1);
INSERT 20166 1
INSERT INTO SLAVE VALUES (1);
INSERT 20167 1

UPDATE MASTER SET id = 10 WHERE id = 1;
ERROR:  slave_primary_check: tuple references non-existing key in master

My intent is to have changes in id number in master reflected by
appropriate changes in slave.

Am I doing something wrong or is refint not capable of doing cascading
updates due to the mismatched (temporarily) id numbers during the update?

Any help greatfully appreciated.

Cheers
Brad Saxton                                     [EMAIL PROTECTED]
Unix System Support                             Phone: (905)688-5550 x3496
Computing and Communications Services, F301
Brock University