Re: On delete cascade not working

Ignore this!! This is has been posted under "[SQL] Referential
Integrity".
This is a previous attempt to post which has finally come through!!

-----Original Message-----
From: Alexander Stanier
Sent: 10 February 2003 09:52
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] 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(at)egsgroup(dot)com <mailto:alex(at)egsgroup(dot)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.