Re: referential integrity error

On Tue, 11 Jun 2002, [ISO-8859-1] Matej Holl wrote:

> /*
> If a table contains two or more foreign keys referencing the same
> table and they reference the same record in the other table, the
> record's primary key cannot be changed even if the keys have the
> "ON UPDATE CASCADE" clause (see example below). It will say that
> "referential integrity error - key referenced from `table' not found
> in `table'".
> */

>
> CREATE TABLE "Bug1" (
> "ID" INT4,
> "Description" TEXT NOT NULL,
> PRIMARY KEY("ID")
> );
> CREATE TABLE "Bug2" (
> "ID" INT4,
> "Source" INT4 NOT NULL
> REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION,
> "Destination" INT4 NOT NULL
> REFERENCES "Bug1" ON UPDATE CASCADE ON DELETE NO ACTION,
> "Description" TEXT NOT NULL,
> PRIMARY KEY ("ID")
> );
> INSERT INTO "Bug1" VALUES (1,'Place');
> INSERT INTO "Bug2" VALUES (10,1,1,'Move from Place to Place');
> UPDATE "Bug1" SET "ID"=2 WHERE "ID"=1;

This works in current sources. There's a patch floating around which
needs a little work but can be used as a basis to patch earlier versions
into working for this case (see archives).