Home » SQL & PL/SQL » Client Tools » ASKING AGAIN ON Positioned Delete
ASKING AGAIN ON Positioned Delete [message #25871] Wed, 21 May 2003 18:35 Go to next message
SQL Chap
Messages: 2
Registered: May 2003
Junior Member
I like to ask a question.There are 3 tables TV3CLMS and TV3CLMT and TV3WKLS. Dependencies are like this. CLMSCLMT,CLMSWKLS that is (parentchild) ex CLMS
Is parent and CLMT is child. Now I am using a "CURRENT OF" to delete rows from TV3WKLS, so there is declare cursor then fetch cursor and then delete current of
EXEC SQL
DELETE FROM TV3WKLS
WHERE CURRENT OF SI_CSR
END-EXEC.
WHEN DELETE IS EXECUTED IT GIVES SQLDODE OF -532(RI VIOLATION) ON CLMSCLMT. WHY IS CLMSCLMT PREVENTING DELETING FROM TV3WKLS The declare cursor looks like this
EXEC SQL
DECLARE SI_CSR CURSOR WITH HOLD FOR
SELECT WORK_LIST_KEY,
CRTN_DATE,
SYSTM_GNRTD_FLAG,
MLSTN_CODE,
MLSTN_TEXT,
CLAIM_KEY,
CLAIM_NO,
MLSTN_TITLE,
UPDT_EMPLY_ID
FROM TV3WKLS
WHERE (TV3WKLS.MLSTN_CODE = :WS-002 OR
TV3WKLS.MLSTN_CODE = :WS-003 OR
TV3WKLS.MLSTN_CODE = :WS-004 OR
TV3WKLS.MLSTN_CODE = :WS-005)
AND EXISTS
(SELECT CLAIM_NO
FROM TV3CLMS
WHERE TV3CLMS.CLAIM_KEY = TV3WKLS.CLAIM_KEY
WHERE TV3WKLS.CLAIM_KEY = TV3CLMS.CLAIM_KEY
AND TV3CLMS.TWO_DSPLY_TYPE = :WS-SI-CLAIM)
END-EXEC.
HERE IS THE ERROR REPORT OF DSNTIAR CALL
DSNTAR-ER-TX1: DSNT408I SQLCODE = -532, ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH RID X'0000005A06'
DSNTAR-ER-TX2: DSNT418I SQLSTATE = 23504 SQLSTATE RETURN CODE
DSNTAR-ER-TX3: DSNT415I SQLERRP = DSNXRDWC SQL PROCEDURE DETECTING ERROR
DSNTAR-ER-TX4: DSNT416I SQLERRD = -250 13172769 0 13228485 -742129664 0 SQL DIAGNOSTIC INFORMATION
DSNTAR-ER-TX5: DSNT416I SQLERRD = X'FFFFFF06' X'00C90021' X'00000000' X'00C9D9C5' X'D3C40000' X'00000000' SQL DIAGNOSTIC
DSNTAR-ER-TX6: INFORMATION
DSNTAR-ER-TX7:
DSNTAR-ER-TX8:
WS-SYS-ERR-FMT = DSNT408I SQLCODE = -532, ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH
WS-SQLCODE-ERROR-MSG = ERROR: THE RELATIONSHIP CLMSCLMT RESTRICTS THE DELETION OF ROW WITH
----------------------------------------------------------------------------------------------------------------------------------------------------------
QUESTION:WHY IS CLMSCLMT GETTING IN THE WAY OF DELETING A ROW FROM TV3CWKLS.
QUESTION:WHY DOES IT THINK WE ARE TRYING TO DELETE FORM TV3CLMS TABLE.
I WILL APPRECIATE VERY MUCH IF YOU CAN SHED SOME LIGHT ON THIS.
All tables are delcared with RESTRICT NOT CASSCADE.
Re: ASKING AGAIN ON Positioned Delete [message #25991 is a reply to message #25871] Fri, 30 May 2003 10:45 Go to previous message
Sud
Messages: 63
Registered: September 2002
Member
Make sure that CLMT and CLMS table dont have a foreign key which references to a key in the WKLS table. Since the DELETE clause declared with RESTRICT, a parent can not be deleted unless its referenced/child row is removed first. If CLMT or CLMS table has any of their columns refer to a column in the WKLS, then you CAN NOT remove a row in WKLS until you remove that referenced row from CLMT and/or CLMS table first, because of their DELETE RESTRICTion given to their foreign key. If still the problem persists, you better to post the copy of those table descriptions here.
Check it out.
Good luck :)
Previous Topic: Delete Duplicate Records from Table
Next Topic: Getting TOAD working
Goto Forum:
  


Current Time: Wed Aug 05 20:59:04 CDT 2020