Re: why it doesn't work? referential integrity

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