ON DELETE SET NULL clauses do error when more than two columns are referenced to one table

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема ON DELETE SET NULL clauses do error when more than two columns are referenced to one table
Дата
Msg-id 162867790708111100x3775c51bi53dbecabb97d14b7@mail.gmail.com
обсуждение исходный текст
Ответы Re: ON DELETE SET NULL clauses do error when more than two columns are referenced to one table
Список pgsql-bugs
Hello

One question on www.abclinuxu.cz signalise bug in PostgreSQL RI
implementation. Detected on 8.0.x and verified on 8.3.

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""

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Magnus Hagander
Дата:
Сообщение: Re: BUG #3512: buggy install + no manual support
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: ON DELETE SET NULL clauses do error when more than two columns are referenced to one table