why it doesn't work? referential integrity
От | Pavel Stehule |
---|---|
Тема | why it doesn't work? referential integrity |
Дата | |
Msg-id | 162867790708110328gf5a045dtf388d4b1e9e640d9@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: why it doesn't work? referential integrity
Re: why it doesn't work? referential integrity Re: why it doesn't work? referential integrity |
Список | pgsql-general |
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""
В списке pgsql-general по дате отправления: