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". On Monday, 10 June 2013 15:02:31 UTC+2, Aaron Zeckoski wrote: > > 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. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
