Skip to Content

Identifying foreign key constraint violation errors

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jan 08, 2014 at 12:07 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.