Oracle to Azure SQL Migration – Constraint name missing when using Hibernate SQLServer Dialect
Published Mar 29 2022 10:05 AM 6,776 Views
Microsoft

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.

 

faizchachiya_0-1648386962296.png

 

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.

 

faizchachiya_1-1648387106125.png

 

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

faizchachiya_0-1648535577903.png

 

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.

 

faizchachiya_24-1648406142751.png

 

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, 

 

Co-Authors
Version history
Last update:
‎Mar 29 2022 09:44 AM
Updated by: