BUG #1740: Deferred foreign key constraint isn't deferred

Поиск
Список
Период
Сортировка
От Daniel Cristian Cruz
Тема BUG #1740: Deferred foreign key constraint isn't deferred
Дата
Msg-id 20050630130139.DD5F7F0AC6@svr2.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #1740: Deferred foreign key constraint isn't deferred  (Michael Fuhr <mike@fuhr.org>)
Re: BUG #1740: Deferred foreign key constraint isn't deferred  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-bugs
The following bug has been logged online:

Bug reference:      1740
Logged by:          Daniel Cristian Cruz
Email address:      dccruz@mega.com.br
PostgreSQL version: 8.0.3
Operating system:   Windows XP Professional
Description:        Deferred foreign key constraint isn't deferred
Details:

Below there is a script which raises the bug:

CREATE TABLE mat_comissao_itens (
    mat_nfs_diretorio character varying(6),
    mat_nfs_in_codigo integer,
    mat_nsi_in_ordem integer
);
CREATE TABLE mat_nf_saida_itens (
    mat_nfs_diretorio character varying(6) NOT NULL,
    mat_nfs_in_codigo integer NOT NULL,
    mat_nsi_in_ordem integer,
    PRIMARY KEY (mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
);
COPY mat_comissao_itens (mat_nfs_diretorio, mat_nfs_in_codigo,
mat_nsi_in_ordem) FROM stdin;
001001  339 1
001001  339 1
001001  339 1
001001  339 2
001001  339 2
001001  339 2
\.
COPY mat_nf_saida_itens (mat_nfs_diretorio, mat_nfs_in_codigo,
mat_nsi_in_ordem) FROM stdin;
001001  339 1
001001  339 2
\.
ALTER TABLE ONLY mat_comissao_itens
    ADD CONSTRAINT mat_nf_saida_itens_pa_mat_comissao_itens FOREIGN KEY
(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem) REFERENCES
mat_nf_saida_itens(mat_nfs_diretorio, mat_nfs_in_codigo, mat_nsi_in_ordem)
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;

BEGIN;
DELETE FROM mat_nf_saida_itens WHERE mat_nsi_in_ordem = 2;
UPDATE mat_comissao_itens SET mat_nsi_in_ordem = 1 WHERE mat_nsi_in_ordem =
2;
COMMIT;

And here is the output:

CREATE TABLE
psql:rafa2.backup:11: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "mat_nf_saida_itens_pkey" for table "mat_nf_saida_itens"
CREATE TABLE
ALTER TABLE
BEGIN
psql:rafa2.backup:28: ERROR:  update or delete on "mat_nf_saida_itens"
violates foreign key constraint "mat_nf_saida_itens_pa_mat_comissao_itens"
on "mat_comissao_itens"
DETAIL:  Key
(mat_nfs_diretorio,mat_nfs_in_codigo,mat_nsi_in_ordem)=(001001,339,2) is
still referenced from table "mat_comissao_itens".
psql:rafa2.backup:29: ERROR:  current transaction is aborted, commands
ignored until end of transaction block
ROLLBACK

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

Предыдущее
От: Stephan Szabo
Дата:
Сообщение: Re: Deferred Referential Constraint: Changing Referenced Key
Следующее
От: "庞劲松@GDLC技术部"
Дата:
Сообщение: Bugs