[GENERAL] referential integrity

[Ian Turner]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It seems that cascading across multiple tables does not work
correctly, when combining different action types. For example, given:

CREATE TABLE a (anum Integer PRIMARY KEY);
CREATE TABLE b (bnum Integer PRIMARY KEY,
                anum Integer REFERENCES a ON DELETE CASCADE);
CREATE TABLE c (cnum Integer PRIMARY KEY,
                bnum Integer REFERENCES b ON DELETE CASCADE,
                anum Integer REFERENCES a ON DELETE SET NULL);

INSERT INTO a (anum) VALUES (1);
INSERT INTO b (bnum, anum) VALUES (1,1);
INSERT INTO c (cnum, bnum, anum) VALUES (1,1,1);

This passes without an error:

delete from b where bnum = 1;
delete from a where anum = 1;

but this fails:

delete from a where anum = 1;

with this error:

ERROR:  <unnamed> referential integrity violation - key referenced from c
not found in b

Got any ideas? :o

Ian Turner
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5rvvXfn9ub9ZE1xoRAqEkAJ0aFgzuoaK23xdnzsE7ADGrCYErRQCfbEjy
X/J9RlqAPHOwnHGlQHTmFRE=
=BgK8
-----END PGP SIGNATURE-----