DB2: Find a foreign key

By | March 15, 2016

Sometimes working with Hibernate you get the following error in case a persist is executed on an object with dependencies.

com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: The insert or update value of the FOREIGN KEY "INST1.MESSAGE.FK_1FUTL48XT47HPPGH0YXQM1MIJ" is not equal to any value of the parent key of the parent table.. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.13.127

From IBM Knowledge Base we get the following explanation:

SQL0530N
The insert or update value of the FOREIGN KEY constraint-name is not equal to any value of the parent key of the parent table.
Explanation

A value in a foreign key of the object table is being set, but this value is not equal to any value of the parent key of the parent table.

When a row is inserted into a dependent table, the insert value of a foreign key must be equal to some value of the parent key of any row of the parent table of the associated relationship.

Similarly, when the value of a foreign key is updated, the update value of a foreign key must be equal to the value of the parent key of any row of the parent table of the associated relationship at the completion of the statement.

Federated system users: the constraint can exist on the data source (if the child and parent tables exist on the data source).

Some data sources do not provide the appropriate values for constraint name. In these cases the message token will have the following format: “:UNKNOWN”, indicating that the actual value for the specified data source is unknown.

The statement could not be executed. The contents of the object table are unchanged.
User response

Examine the insert or update value of the foreign key first, and then compare it with each of the parent key values of the parent table to determine and correct the problem.

sqlcode: -530

sqlstate: 23503

OK we got it but how to detect to which relation is that foreign key from the hibernate error refers.
This can be easily done by a query as the following:

select * from SYSCAT.REFERENCES where constname='FK_1FUTL48XT47HPPGH0YXQM1MIJ';

This will have a result with the header:

So now we can read the Hibernate error as:

In TABNAME there is an FK_COLNAMES that is the PK_COLNAMES from REFTABNAME and issue is that PK_COLNAMES does not exist with the given id.

Advertisements