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.
