Have you encountered a missing constraint name in your application error messages, when migrating your application from Oracle to Azure SQL?
During a recent such migration, a customer ran across the following issue in their Java application, where the constraint name was missing for constraint-related errors, and just showed 'null'.
The application team used Spring and Hibernate Java frameworks for performing data operations against Azure SQL. The constraint exception was of org.hibernate.exception.ConstraintViolationException class type. Within this class, there was the method "getConstraintName()" which returned 'null' instead of the constraint name. Below is the definition of the method from the official documentation.
The actual exception message showed the constraint name, but then explicitly retrieving the constraint name returned 'null', which you can see in the following screenshot.
After some analysis, we discovered that the Hibernate code extracting the constraint name from the exception was returning 'null'.
The below matrix shows the class hierarchies for both Oracle and Azure SQL dialects, and excerpt from the Hibernate source code that proved that the issue existed within the Hibernate library
Solution
Given the limitations within the Hibernate library, we recommended that the constraint name in the actual error message can be easily extracted by writing a regular expression. For example, if the error message was "Violation of UNIQUE KEY constraint 'UC_Person2'", the constraint name could be easily extracted using Regex expression '(.*?)'. See the following screenshot for reference.
Note: The issue persists for even latest versions of Hibernate library, the source code for Hibernate dialect is available here for reference.
What’s next?
If you are in the process of migrating your database from Oracle to Azure SQL,
- Leverage the SQL Server Migration Assistant for Oracle (SSMA)
- Install the Data Access Migration Toolkit extension