SEO

The following bug has been logged online:

Bug reference: 2119
Logged by: Fred Perniß
Email address: perniss@zks-gmbh.de
PostgreSQL version: 8.02
Operating system: Suse Linux 9.1
Description: FOREIGN KEY ON DELETE RESTRICT
Details:

I have 2 tables
CREATE TABLE depot
(
dkt_id int4 NOT NULL DEFAULT nextval('"depot_dkt_id_seq"'::text),
dkt_kto_id int4
CONSTRAINT depot_pkey PRIMARY KEY (dkt_id)
)
WITH OIDS;

CREATE TABLE p_kto
(
kto_id int4 NOT NULL DEFAULT nextval('"p_kto_kto_id_seq"'::text)
)
WITH OIDS;

And I have a foreign key

ALTER TABLE depot
ADD CONSTRAINT c_dkt_kto_id FOREIGN KEY (dkt_kto_id) REFERENCES p_kto
(kto_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


If I delete a dataset from p_kto then is also delete the referenced sets in
depot.
Normaly the RESTRICT should prevents deletion. Or?

Restricting and cascading deletes are the two most common options. RESTRICT
prevents deletion of a referenced row. NO ACTION means that if any
referencing rows still exist when the constraint is checked, an error is
raised; this is the default behavior if you do not specify anything. (The
essential difference between these two choices is that NO ACTION allows the
check to be deferred until later in the transaction, whereas RESTRICT does
not.) CASCADE specifies that when a referenced row is deleted, row(s)
referencing it should be automatically deleted as well. There are two other
options: SET NULL and SET DEFAULT. These cause the referencing columns to be
set to nulls or default values, respectively, when the referenced row is
deleted. Note that these do not excuse you from observing any constraints.
For example, if an action specifies SET DEFAULT but the default value would
not satisfy the foreign key, the operation will fail.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Fred PerniÃ" <perniss@zks-gmbh.de> writes:
> If I delete a dataset from p_kto then is also delete the referenced sets in
> depot.
> Normaly the RESTRICT should prevents deletion. Or?

Works for me:
regression=# delete from p_kto where kto_id = 101;
ERROR: update or delete on "p_kto" violates foreign key constraint "c_dkt_kto_id" on "depot"
DETAIL: Key (kto_id)=(101) is still referenced from table "depot".

I think you've not told us the full truth about your tables. The quoted
definitions are syntactically wrong (missing commas) and semantically
wrong (kto_id has no UNIQUE or PRIMARY KEY constraint, therefore can't
be the target of a foreign key reference). I think you stripped out
everything you thought was irrelevant, including the actual source of
the problem --- maybe there is another constraint that is ON DELETE
CASCADE, and it happens to get processed first?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org