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