Re: On delete cascade not working - Mailing list pgsql-sql

I am running postgresql 7.3.1 on cygwin and am having problem with an "on delete cascade".

I have two tables:

CREATE TABLE catalogItem
(
                            contractNumber varchar (40) NOT NULL,
      -- REFERENCES contractVendor (contractNumber)
                            clin varchar (100) NOT NULL,
                            categoryId varchar (20) NOT NULL,
                            manufProdId varchar (200) NOT NULL,
                            manufId varchar (40) NOT NULL,
                            itemDesc varchar (512),
                            warrantyPeriod varchar (100),
                            prodUrl varchar (500),
                            bundleUrl varchar (4000),
                            openMarketFlag varchar (1),
                            featuresCount integer,
                            upgradesCount integer,
                            optionsCount integer,
                            selectionsCount integer,
                            accessoriesCount integer,
                            modDate timestamp,
    PRIMARY KEY (contractNumber,clin)
);

AND

CREATE TABLE catalogItemVatCode
(
                            contractNumber varchar (40) NOT NULL,
      -- REFERENCES catalogItem (contractNumber, clin)
                            clin varchar (100) NOT NULL,
      -- REFERENCES catalogItem (contractNumber, clin)
                            vatCode varchar (10) NOT NULL,
                            modDate timestamp,
    PRIMARY KEY (contractNumber,clin)
);

I also have a constraint between them as follows:

ALTER TABLE catalogItemVatCode
    ADD CONSTRAINT catalogItemVatCode_FK_1 FOREIGN KEY (contractNumber, clin)
    REFERENCES catalogItem (contractNumber, clin)
    ON DELETE CASCADE
;

I also have a function which should replace records in these tables with records queried from various "staging" tables, amongst other things:

CREATE FUNCTION movetoprod(int4) RETURNS INT4 AS '
DECLARE
    v_stageentryid ALIAS FOR $1;
    stageitem_row RECORD;
    v_return INTEGER;
BEGIN
    FOR stageitem_row IN SELECT contractnumber, clin, manufprodid, manufid,
                                categoryid, itemdesc, warrantyperiod, produrl,
                                bundleurl, openmarketflag, updatetype, movedtoprod
                           FROM stageitem, stageentry
                          WHERE stageitem.stageentryid = stageentry.stageentryid
                            AND mappingcheck = ''Y''
                            AND mappingvalid = ''Y''
                            AND approved = ''Y''
                            AND stageitem.stageentryid = v_stageentryid
    LOOP

      IF stageitem_row.movedtoprod IS NULL THEN

            IF stageitem_row.updatetype IN (''Add'',''Modify'',''Delete'') THEN

                DELETE FROM catalogitem
                 WHERE contractnumber = stageitem_row.contractnumber
                   AND clin = stageitem_row.clin;

            END IF;

            IF stageitem_row.updatetype IN (''Add'',''Modify'') THEN

               INSERT INTO catalogitem
                           (contractnumber, clin, categoryid, manufprodid,
                            manufid, itemdesc, warrantyperiod, produrl,
                            bundleurl, openmarketflag, featurescount,
                            upgradescount, optionscount, selectionscount,
                            accessoriescount, moddate)
                    VALUES (stageitem_row.contractnumber,
                            stageitem_row.clin,
                            stageitem_row.categoryid,
                            stageitem_row.manufprodid,
                            stageitem_row.manufid,
                            stageitem_row.itemdesc,
                            stageitem_row.warrantyperiod,
                            stageitem_row.produrl,
                            stageitem_row.bundleurl,
                            stageitem_row.openmarketflag,
                            0, 0, 0, 0, 0, now());

               INSERT INTO catalogitemvatcode
                              (contractnumber,
                               clin,
                               vatcode, moddate)
                       SELECT  contractnumber,
                               clin,
                               vatcode, now()
                         FROM stageitemvatcode, stageentry
                        WHERE stageitemvatcode.stageentryid = stageentry.stageentryid
                          AND stageitemvatcode.clin = stageitem_row.clin
                          AND stageitemvatcode.stageentryid = v_stageentryid;

             END IF;

      END IF;

    END LOOP;

    RETURN 0;
END;
' LANGUAGE 'plpgsql';

This fails with:

WARNING:  Error occurred while executing PL/pgSQL function movetoprod
ERROR:  Cannot insert a duplicate key into unique index catalogitemvatcode_pkey

However, the delete statement within the function should ensure that not only has the catalogitem record been deleted, but, by virtue of the the constraint which contains an 'ON DELETE CASCADE', the corresponding catalogitemvatcode record is also deleted. I have have checked the constraint by manually deleting a record from catalogitem and it works - the catalogitemvatcode record is removed too. Why when this is done in a function does the 'ON DELETE CASCADE' not function?

Alexander Stanier
mailto:alex@egsgroup.com

E Government Solutions Ltd, 3rd Floor, Domain House, 5 - 7 Singer Street, London EC2A 4BQ.
Direct Dial: +44 (20) 7336 1441
Mobile: +44 (0) 7774 779394
Switchboard: +44 (20) 7336 1440
www.egsgroup.com

CONFIDENTIALITY: This is email is confidential and intended solely for the use of the individual to whom it is addressed.  Any views or opinions presented are solely those of the author and do not necessarily represent those of E Government Solutions Ltd.  If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, or copying of this email is strictly prohibited.  If you have received this email in error please contact the sender.