On Saturday 11 August 2007 12:28:45 Pavel Stehule wrote: > Hello > > I found strange postgresql's behave. Can somebody explain it? > > Regards > Pavel Stehule > > CREATE TABLE users ( > id integer NOT NULL, > name VARCHAR NOT NULL, > PRIMARY KEY (id) > ); > > INSERT INTO users VALUES (1, 'Jozko'); > INSERT INTO users VALUES (2, 'Ferko'); > INSERT INTO users VALUES (3, 'Samko'); > > CREATE TABLE tasks ( > id integer NOT NULL, > owner INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, > worker INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL, > checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET > NULL, PRIMARY KEY (id) > ); > INSERT INTO tasks VALUES (1,1,NULL,NULL); > INSERT INTO tasks VALUES (2,2,2,NULL); > INSERT INTO tasks VALUES (3,3,3,3); > > DELETE FROM users WHERE id = 1; -- works simple > DELETE FROM users WHERE id = 2; -- works ok > DELETE FROM users WHERE id = 3; -- doesn't work, why? > > ERROR: insert or update on table "tasks" violates foreign key > constraint "tasks_checked_by_fkey" > DETAIL: Key (checked_by)=(3) is not present in table "users". > CONTEXT: SQL statement "UPDATE ONLY "public"."tasks" SET "worker" = > NULL WHERE $1 OPERATOR(pg_catalog.=) "worker"" looks strange to me too, but i never had foreign keys to the same table. it works if you define your chekced_by FK deferrable with checked_by INT REFERENCES users (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, it seams that postgresql does its job in a procedural way instead of relational. kind regards, Janning ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
