H2 database Unsuccessful schema statement when add index and foreign key constraint

[Aaron Zeckoski]

I also posted this to stackoverflow since I wasn't sure which way of asking questions like this is preferred.

http://stackoverflow.com/questions/17024482/h2-database-unsuccessful-schema-statement-when-add-index-and-foreign-key-constra

H2 does not appear to support the index/FK syntax generated by hibernate for the MySQL5 dialects when using ;MODE=MYSQL and hibernate with a dialect of org.hibernate.dialect.MySQL5Dialect.

My goal here is to have one set of SQL scripts and use hibernate for the ORM parts. Everything works fine in MySQL 5.5 but when I try to use H2 for things like unit tests and starting up a demo version of my app I get hundreds of failures from hibernate generated alter table statements as shown below. Unfortunately, I have not been able to find a way to get hibernate to change the way the statements are generated but that might be an option as well. I tried using org.hibernate.dialect.H2Dialect but that produces more severe errors so I don't think that will work.

alter table SAM_PUBLISHEDSECUREDIP_T
  add index FK1EDEA25B9482C945 (ASSESSMENTID), 
  add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) 
  references SAM_PUBLISHEDASSESSMENT_T (ID)

Results in an error like this in H2:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE SAM_PUBLISHEDSECUREDIP_T ADD INDEX FK1EDEA25B9482C945 (ASSESSMENTID),[*] ADD CONSTRAINT FK1EDEA25B9482C945 FOREIGN KEY (ASSESSMENTID) REFERENCES SAM_PUBLISHEDASSESSMENT_T (ID) "; SQL statement: alter table SAM_PUBLISHEDSECUREDIP_T add index FK1EDEA25B9482C945 (ASSESSMENTID), add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) references SAM_PUBLISHEDASSESSMENT_T (ID) [42000-172]

NOTE: I am open to writing and providing a patch for H2 but I could use some tips on where to look in that codebase.

[Steve McLeod]

H2 doesn't currently support adding an index AND a constraint in one SQL statement. H2 needs two statements, which would be 

alter table SAM_PUBLISHEDSECUREDIP_T
  add index FK1EDEA25B9482C945 (ASSESSMENTID)

and 

alter table SAM_PUBLISHEDSECUREDIP_T

  add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) 
  references SAM_PUBLISHEDASSESSMENT_T (ID)

It would be nice if you can create a patch to fix this. I believe it would be relatively straight-forward once you get familiar with how H2 handles "alter table".

[Steve McLeod]

A good place to start understanding the source is the org.h2.command.ddl package, where you'll find both the AlterTableAddConstraint class and the CreateIndex command.

BTW: my previous gave the wrong syntax for adding an index. It is:

create index FK1EDEA25B9482C945 on SAM_PUBLISHEDSECUREDIP_T (ASSESSMENTID)