Identifying foreign key constraint violation errors

[Former Member]

Whenever I get a Foreign Key constraint violation (such as below) the message does not indicate what table, column, or constraint is involved.  Sometimes I can work it out from the line-number in the procedure, but is there a more direct and informative way?

(I'm using SPS6)

Could not execute 'call P_POPULATE_BASE_DATA(0)' in 6.470 seconds .

SAP DBTech JDBC: [461]: foreign key constraint violation:  [461] RESDET.P_POPULATE_BASE_DATA: line 298 col 3 (at pos 13528): foreign key constraint violation exception: foreign key constraint violation

[acaireta]

For your case you must use the EXIT HANDLER statement, you can find more information in http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

To find the listing of SQL_ERROR_CODE, see the following document:

http://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf

Then I put an example of use of the judgment EXIT HANDLER with error 'Foreing Key violation'.

-------------------------------------------------------------------------------------------------------------------------------------------------------

DROP TABLE "CUSTOMER"; 

CREATE COLUMN TABLE "CUSTOMER" ( 

          CUSTID          INT, 

          NAME          VARCHAR(60), 

          ADDRESS          VARCHAR(60), 

          PRIMARY KEY (CUSTID) 

          );  

DROP TABLE "ORDER"; 

CREATE COLUMN TABLE "ORDER" ( 

          ORDID          INT, 

          CUSTID          INT, 

          ORDDATE          DATE, 

          NETVAL          DECIMAL(23,2), 

          PRIMARY          KEY (ORDID), 

          FOREIGN KEY (CUSTID) REFERENCES "CUSTOMER" 

          ); 

DROP PROCEDURE TEST_INSERT_ORDER;

CREATE PROCEDURE TEST_INSERT_ORDER(ORDID INT) AS

BEGIN

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 461

BEGIN

--In your case you have validate what foreingkey constaint is produce the error.

SELECT 'Value of CUSTID not exist!', ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

END;

INSERT INTO "ORDER" (ORDID,CUSTID,ORDDATE,NETVAL) VALUES (:ORDID,1,NOW(),10.5);

END;

CALL TEST_INSERT_ORDER(1)

-------------------------------------------------------------------------------------------------------------------------------------------------------

Regards.

[Former Member]

Thanks Adria,

But the EXIT_HANDLER does not seem to be working out what exactly the violation is.  The :SQL_ERROR and :SQL_ERROR_MESSAGE are not very informative.   If the procedure contained many INSERT statements, each involving many columns that could be producing the violation, the EXIT_HANDLER would not be able to determine the problem.

When a 461 Foreign Key Constraint Violation occurs, is there no error information anywhere to indicate exactly what table and column is being violated?

David

[acaireta]

Hi,

Indeed the error message does not indicate which column is affected, it would be good to enter a note to sap to improve more information of message error.

Regards.