identity colomn

[Gmail]

Hi,

When executing the script below on 1.4.200 I get the result below :

DBMS  : H2 1.4.200 (2019-10-14)
JDBC  : H2 JDBC Driver 1.4.200 (2019-10-14)

create  table ftypes(
     ID IDENTITY primary key ,
     types VARCHAR
 );

insert into ftypes (id,types) values(1,'R1');
insert into ftypes (id,types) values(2,'R2');
insert into ftypes (id,types) values(3,'R3');
insert into ftypes (id,types) values(default,'R4');
insert into ftypes (id,types) values(null,'R5');
insert into ftypes (types) values('R6');

select * from ftypes;
-------------------------------------------
|         ID         |        TYPES       |
-------------------------------------------
|          1         |         R1         |
|          2         |         R2         |
|          3         |         R3         |
|          4         |         R4         |
|          5         |         R5         |
|          6         |         R6         |
-------------------------------------------

But on 2.0.202 I get the following errors:

DBMS  : H2 2.0.202 (2021-11-25)

create  table ftypes(
     ID IDENTITY primary key ,
     types VARCHAR
 );

insert into ftypes (id,types) values(1,'R1');
insert into ftypes (id,types) values(2,'R2');
insert into ftypes (id,types) values(3,'R3');
insert into ftypes (id,types) values(default,'R4');
Err: java.sql.SQLException:  Unique index or primary key violation:
"PRIMARY KEY ON PUBLIC.FTYPES(ID) ( /* key:1 */ CAST(1 AS BIGINT),
'R1')"; SQL statement:
insert into ftypes (id,types) values(default,'R4') [23505-202]
insert into ftypes (id,types) values(null,'R5');
Err: java.sql.SQLException:  NULL not allowed for column "ID"; SQL
statement:
insert into ftypes (id,types) values(null,'R5') [23502-202]
insert into ftypes (types) values('R6');
Err: java.sql.SQLException:  Unique index or primary key violation:
"PRIMARY KEY ON PUBLIC.FTYPES(ID) ( /* key:2 */ CAST(2 AS BIGINT),
'R2')"; SQL statement:
insert into ftypes (types) values('R6') [23505-202]

select * from ftypes;
-------------------------------------------
|         ID         |        TYPES       |
-------------------------------------------
|          1         |         R1         |
|          2         |         R2         |
|          3         |         R3         |
-------------------------------------------

Question : Is there a way to set the behaviour  in 2.0.202 the same as
in 1.4.200 ?

Thanks in advance and very good job to all of you

willy

[Evgenij Ryazanov]

Hello.

Mode mode = Mode.getRegular();

mode.updateSequenceOnManualIdentityInsertion = true;

mode.identityColumnsHaveDefaultOnNull = true;

should restore old incorrect behavior (influenced by historic versions of MySQL and others), but it would be better to fix your application instead.

Please note that flags in org.h2.engine.Mode class aren't supported and they may be modified or removed in any version without a notice.

Also you normally should declare your column with ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY.

IDENTITY as a data type still works, but it isn't supported any more.

[Gmail]

Hi,

thanks for the update and clarification.

Kind regards

willy

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/b3c68051-8799-46a9-b79e-155b4c818215n%40googlegroups.com.