In H2 Database, add index while table creation in single query

Question

I am trying to create table having different indexes with single query but H2 gives Error for example:

create table tbl_Cust
(
  id int primary key auto_increment not null, 
  fid int,
  c_name varchar(50),
  INDEX (fid)
);

but this gives error as

Unknown data type: "("; SQL statement:
[Error Code: 50004]
[SQL State: HY004]

Due to this I have to run 2 different queries to create table with Index. First query to create table and then second query to add index with

create INDEX c_fid on tbl_Cust(fid);

Is there something wrong in my query or H2 simply does not support this creation of table with index in single query?

Answer 1

Interesting question. The solution is even more interesting, as it involves MySQL compatibility mode.

It's actually possible to perform the exact same command you wrote without any modification, provided you just add to your jdbc url the MySQL mode.

Example URL like this: jdbc:h2:mem:;mode=mysql

SQL remains:

create table tbl_Cust
(
  id int primary key auto_increment not null, 
  fid int,
  c_name varchar(50),
  INDEX (fid)
);
Update count: 0
(15 ms)

Too bad I did not see this question earlier... Hopefully the solution might become handy one day to someone :-)