Re: referential integrity error

On Tue, 11 Jun 2002, [ISO-8859-1] Matej Holl=FD 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"=3D2 WHERE "ID"=3D1;

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).