Referential Integrity - Mailing list pgsql-bugs

<p><font face="Comic Sans MS" size="2">Dear pgsql-sql,</font><p><font face="Comic Sans MS" size="2">(Third time of
tryingto post this message - apologies if any previous attempts subsequently succeed!! Ignore them this one states the
casemore clearly)</font><p><font face="Comic Sans MS" size="2">I have the following scenario in
pg7.3.1.:</font><p><fontface="Courier New" size="2">CREATE TABLE alex (aid varchar(40) NOT NULL,</font><br /><font
face="CourierNew" size="2">                   bid varchar(100) NOT NULL,</font><br /><font face="Courier New"
size="2">                  itemdesc varchar(100),</font><br /><font face="Courier New" size="2">        CONSTRAINT
alex_pkeyPRIMARY KEY (aid, bid));</font><p><font face="Courier New" size="2">CREATE TABLE alexette (aid varchar(40) NOT
NULL,</font><br/><font face="Courier New" size="2">                       bid varchar(100) NOT NULL,</font><br /><font
face="CourierNew" size="2">                       vcode varchar(1),</font><br /><font face="Courier New"
size="2">           CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));</font><p><font face="Courier New" size="2">ALTER
TABLEalexette </font><br /><font face="Courier New" size="2">ADD CONSTRAINT alexette_fk_1 FOREIGN KEY
(aid,bid)</font><br/><font face="Courier New" size="2">    REFERENCES alex (aid,bid)</font><br /><font face="Courier
New"size="2">    ON DELETE CASCADE;</font><p><font face="Courier New" size="2">INSERT INTO alex VALUES
('1','1','OneOne');</font><p><fontface="Courier New" size="2">INSERT INTO alexette VALUES ('1','1','V');</font><p><font
face="CourierNew" size="2">CREATE FUNCTION test() RETURNS INT4 AS '</font><br /><font face="Courier New"
size="2">DECLARE</font><br/><font face="Courier New" size="2">    v_return INTEGER;</font><br /><font face="Courier
New"size="2">BEGIN</font><br /><font face="Courier New" size="2">  DELETE FROM alex</font><br /><font face="Courier
New"size="2">        WHERE aid = ''1''</font><br /><font face="Courier New" size="2">          AND bid =
''1'';</font><p><fontface="Courier New" size="2">  INSERT INTO alex (aid,bid,itemdesc)</font><br /><font face="Courier
New"size="2">            VALUES (''1'',''1'',''OneOne'');</font><p><font face="Courier New" size="2">  INSERT INTO
alexette(aid,bid,vcode)</font><br /><font face="Courier New" size="2">                VALUES
(''1'',''1'',''V'');</font><p><fontface="Courier New" size="2">  RETURN 0;</font><br /><font face="Courier New"
size="2">END;</font><br/><font face="Courier New" size="2">' LANGUAGE 'plpgsql';</font><br /><p><font face="Comic Sans
MS"size="2">Please could someone tell me why running the function test() causes the following error:</font><p><span
lang="en-gb"><fontface="Comic Sans MS" size="2">"Cannot insert a duplicate key into unique index
alexette_pkey</font><fontface="Times New Roman">"</font></span><p><span lang="en-us"><font face="Comic Sans MS"
size="2">Theforeign key with "on delete cascade" should ensure that the delete statement within this function should
deletethe records from both alex and alexette. Indeed a simple "delete from alex" demonstrates that this does indeed
work.However, it appears that within the function this is not happening or that the insert statements cannot see the
fullextent of the changes made by the delete statement. Whichever, surely this is wrong? Or am I missing
something?</font></span><p><spanlang="en-us"><font face="Comic Sans MS" size="2">Regards,</font></span><p><span
lang="en-us"><fontface="Arial" size="2">Alexander Stanier</font></span><span lang="en-gb"></span><span
lang="en-gb"><fontface="Arial" size="2">.</font></span><span lang="en-us"></span><span lang="en-us"></span><br /><span
lang="en-gb"><fontface="Arial" size="2"><a href="mailto:alex@egsgroup.com">mailto:alex@egsgroup.com</a></font></span>