Обсуждение: referential integrity error

/* If a table contains two or more foreign keys referencing the same table and they reference the same record in the
othertable, the record's primary key cannot be changed even if the keys have the "ON UPDATE CASCADE" clause (see
examplebelow). 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,
PRIMARYKEY ("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;

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