Hi all!
I have the following table, which actually represents a tree:
------------------------------------------------
CREATE TABLE Forum (
ForumId INTEGER CONSTRAINT Forum_PK PRIMARY KEY,
ParentId INTEGER CONSTRAINT Forum_FK1 REFERENCES Forum (ForumId)
ON DELETE SET NULL,
LeftId INTEGER,
RightId INTEGER,
Ranking INTEGER DEFAULT 0,
Name VARCHAR(100),
Description TEXT
);
------------------------------------------------
I have filled the table with some data:
------------------------------------------------
> SELECT forumId, parentId FROM Forum;
forumid | parentid
---------+----------
0 | 0
1 | 0
2 | 0
3 | 0
4 | 3
(5 rows)
------------------------------------------------
Now the interesting part: see the following function which does only two
things, deleting a row and doing an update afterwards (presumably on the
ramaining rows. Dont take it too seriously, this is only for demonstration
purposes):
------------------------------------------------
CREATE OR REPLACE FUNCTION DropTest() RETURNS BOOLEAN AS '
DELETE FROM Forum WHERE ForumId = 0;
UPDATE Forum SET leftId = 0;
SELECT true;
' LANGUAGE 'sql';
------------------------------------------------
and now I get the following:
------------------------------------------------
=> select DropTest();
ERROR: forum_fk1 referential integrity violation - key referenced from forum not found in forum
------------------------------------------------
The funny thing is, that if I do these two statements outside of the function,
there is no violation:
------------------------------------------------
=> DELETE FROM Forum WHERE ForumId = 0; UPDATE Forum SET leftId = 0;
DELETE 1
UPDATE 4
------------------------------------------------
Am I missing something !?
I'm using 7.2 on linux ...
so long
Peter
