Hi,
I was surprised by the following behavior of Postgres (8.1).
Consider the following table and constraint:
=============================================
CREATE TABLE tblissue(
issueid SERIAL PRIMARY KEY,
title TEXT,
comment TEXT,
createtimestamp TIMESTAMP DEFAULT (current_timestamp),
parentissueid INTEGER,
caseclosed CHAR(1)
);
alter table tblissue add constraint
"tblissue_parentissueid_fkey_casc_del" FOREIGN KEY (parentissueid)
REFERENCES tblissue(issueid) ON DELETE CASCADE;
=============================================
So the parentissueid references the same table's PK, but can NULL too.
All fine so far.
Next I insert a few rows that use not null values for parentissueid, so
the foreign key constraint is in effect.
Suppose I created a few rows that have 1 as value for parentissueid.
Then:
delete from tblissue where issueid=1;
DELETE 1
Postgresql now deletes all rows that had a 1 for parentissueid. (5 in my
testcase).
That was correct, and as I intended, but why does Postgres answer
"DELETE 1" instead of DELETE 6?
Can somebody explain that to me please?
Thanks for your time.
Regards,
Erwin Moller
PS: I found a few possible relevant postings. One of them (by Tom Lane)
pointed to here:
http://www.postgresql.org/docs/8.0/static/rules-status.html
but I am still unsure if it is relevant.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
