CODEWITHSUNDEEP
sqlhibernateh2
Aaron Zeckoski
Aaron Zeckoski

Reputation: 5186

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

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.

Upvotes: 1

Views: 4399

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50127

H2 is not 100% compatible with MySQL, even when using the MySQL mode. It seems some of your SQL statements are not supported by H2.

Upvotes: 4

user330315
user330315

Reputation:

Creating an index is not done using alter table (does MySQL really use this strange syntax? Every DBMS I know uses CREATE INDEX to create an index).

You have to split this up in two statements:

CREATE INDEX fk_assessment_id_index 
     on SAM_PUBLISHEDSECUREDIP_T (ASSESSMENTID);

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

Those two statements should also work in MySQL if I'm not mistaken.

Upvotes: 0

Related Questions