Thread: On delete cascade not working
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.