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?

There is no `index` option in the `create table` statement which is clearly documented in the manual: http://h2database.com/html/grammar.html#create_table — , Oct 10 '14 at 09:33
@a_horse_with_no_name I don't find the line stating that index option is not while creating table. Can you put that line here. — , Oct 10 '14 at 10:13
Look at the syntax diagram (the "graph") for the statement. There is no `index` keyword in there. — , Oct 10 '14 at 10:14

score 23 · Answer 1 · edited Feb 25 '20 at 02:19

23

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 :-)

edited Feb 25 '20 at 02:19

ch271828n

15,854
5
53
88

answered Jun 04 '15 at 21:50

Christian MICHON

2,140
2
19
30

1

Your query is not working for me. Giving the same error mentioned in question – MayurB Jul 22 '16 at 06:47
Please share your db URL, your sql query and the version of H2 you're using. – Christian MICHON Jul 22 '16 at 09:30
Thanks Christian! I'm using H2 at 1.4.193 version and it worked like a charm with MySql compatibility mode. – Miere May 04 '18 at 11:54
This does work, thanks! – Art Jan 03 '22 at 05:41

score 7 · Answer 2 · edited Aug 09 '21 at 15:25

I could resolve the problem. According to http://www.h2database.com/html/grammar.html#create_index I modified the query. It works fine with my H2 server.

CREATE TABLE subscription_validator (
    application_id int(11) NOT NULL,
    api_id int(11) NOT NULL,
    validator_id int(11) NOT NULL,
    PRIMARY KEY (application_id,api_id),
    CONSTRAINT subscription_validator_ibfk_1 FOREIGN KEY (validator_id) REFERENCES validator (id) ON UPDATE CASCADE
  );

CREATE INDEX validator_id ON subscription_validator(validator_id);

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

2 Answers2