Jake Stride wrote:
I seem to be having some problems with cascading updates, I seem to
remember that this worked in the database in 7.4 but seems to fail in 8,
can anyone give me any pointer please as I seem to be able to find
anything about this online.
No when I try to do an update I get the following error:
update users set username='new' where username='old';
ERROR: insert or update on table "company" violates foreign key
constraint "$5"
DETAIL: Key (alteredby)=(old) is not present in table "users".
CONTEXT: SQL statement "UPDATE ONLY "public"."company" SET "assigned" =
$1 WHERE "assigned" = $2"
surely this should not fail because of the 'ON UPDATE CASCADE'?
Hmm - I can reproduce it on 8.0.2 on my Mac laptop, but the following
works fine on version 7.4.7 which I also have here.
--
-- Begin test
--
CREATE TABLE foo (a int, b text, PRIMARY KEY (a));
CREATE TABLE bar (
x int,
y int NOT NULL REFERENCES foo(a) ON UPDATE CASCADE,
z int NOT NULL REFERENCES foo(a) ON UPDATE CASCADE
);
INSERT INTO foo VALUES (1,'aaa');
INSERT INTO foo VALUES (2,'bbb');
INSERT INTO bar VALUES (101, 1, 1);
INSERT INTO bar VALUES (102, 2, 2);
UPDATE foo SET a=3 WHERE a=1;
--
-- End test
--
There is something similar mentioned on the bugs list - "Cascading
updates run seperately". Does this look like your problem?
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
