hibernatejdbch2schemaexport

Syntax Error in SQL Statement with H2

I have an error while some sql statements in H2 database. Those sql statements come from a Hibernate SchemaExport : Here are the sql statements :

create table CONTACTS (
    person_id bigint not null,
    contact_id bigint not null,
    primary key (person_id, contact_id)
)

 create table PERSON (
    id bigint generated by default as identity,
    FNAME varchar(55),
    NAME varchar(55),
    primary key (id)
)

alter table CONTACTS 
    add constraint UK_r5plahp7wqcsd47hscckyrxgd unique (contact_id)

alter table CONTACTS 
    add constraint FK_r5plahp7wqcsd47hscckyrxgd 
    foreign key (contact_id) 
    references PERSON

alter table CONTACTS 
    add constraint FK_90n1kse999lanaepr0v6hcgtv 
    foreign key (person_id) 
    references PERSON

For instance, this line won't execute in H2.

The error says : [ERROR] Caused by org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement : CREATE TABLE CONTACTS ( .... <<The same code from above>>

How I can make this SQL statement run in H2.

Solution

  • I finally find the reason why I had the syntax error.

    I am actually running a SchemaExport/SchemaUpdate with Hibernate and I did not specify a delimiter in the SQL statement.

    To specify a delimiter, use the setDelimiter method. For instance, 

    export.setDelimiter(";");
update.setDelimiter(";");

    By the way, to identify syntax errors in H2 with SQL statements, find the * in the statement and it will give the line of your error.