UNIQUE KEY constraint in H2-1.4.199 is not working with H2-2.2.224

[Balamurali Krishna Ippili]

Hi Team,

I have a table schema in my H2 DB like below and when I am doing the build with H2DB 1.4.199 the table query is working fine but when using the H2DB 2.2.224 jar and while doing hte build it was giving "Expected NULLS" issue as shown below:

Create Table Query:

================

CREATE TABLE nim_config_properties (
id INT AUTO_INCREMENT
,mdr_id INT
,property_name VARCHAR(50)
,property_value VARCHAR(400)
,PRIMARY KEY (id)
,FOREIGN KEY (mdr_id) REFERENCES nim_config(mdr_id)
,UNIQUE KEY mdrIdProeprtyName(mdr_id, property_name)
);

Giving the following error when using H2DB 2.2.224 jar  but working fine wiht H2DB 1.4.199 Jar

=================================================================================
Syntax error in SQL statement " CREATE TABLE nim_config_properties (\000a id INT AUTO_INCREMENT\000a ,mdr_id INT\000a ,property_name VARCHAR(50)\000a ,property_value VARCHAR(400)\000a ,PRIMARY KEY (id)\000a ,FOREIGN KEY (mdr_id) REFERENCES nim_config(mdr_id)\000a ,UNIQUE [*]KEY mdrIdProeprtyName(mdr_id, property_name)\000a )"; expected "NULLS, ("; SQL statement:

Any pointers would be highly appriciated.

Thanks in advance.

Regards,

Balamurali

[Evgenij Ryazanov]

Hi!

Your table definition has non-portable MySQL-specific syntax. If this is not your intention, you should use standard CONSTRAINT mdrIdProeprtyName UNIQUE(mdr_id, property_name) instead.

MySQL-specific features are only allowed in MySQL and MariaDB compatibility modes:
https://h2database.com/html/features.html#compatibility
If you use H2 for unit tests with Spring Boot, you also need to disable automatic configuration of databases with @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE) annotation or in any other way.

[Balamurali Krishna Ippili]

Hi 
Evgenij Ryazanov ,

Thank you so much for your quick response and the suggestion. When changed the constraint to a standard  CONSTRAINT mdrIdProeprtyName UNIQUE(mdr_id, property_name) the error is not shown. 

Thanks,

Balamurali