Trouble inserting foreign key with H2

Question

I'm using H2 to test my database code. To do that, I start by reading in a set of SQL files and using RunScript.execute to execute the SQL.

My real database is MySQL, and I have tested the SQL scripts to make sure they work and they do. The problem is decidedly inside of H2. Here's the set of scripts:

CREATE TABLE stores (
  id integer AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL,
  UNIQUE(name),
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at timestamp
);

CREATE TABLE regions (
  id integer AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL,
  UNIQUE(name),
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at timestamp
);


CREATE TABLE products (
  id integer AUTO_INCREMENT PRIMARY KEY,
  store_id integer NOT NULL,
  name varchar(255) NOT NULL,
  region_id integer NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at timestamp,
  UNIQUE(store_id, name),
  FOREIGN KEY store_fk(store_id) REFERENCES stores(id),
  FOREIGN KEY region_fk(region_id) REFERENCES regions(id)
);

Running these 3 scripts to create the tables using MySQL Workbench works fine. Running them from JUnit via the follow code does not:

Reader storeTableSql = new InputStreamReader(
    DatabaseConnectorTests.class.getResourceAsStream(
        "/migrations/V1__create_stores_table.sql"));

Reader regionTableSql = new InputStreamReader(
    DatabaseConnectorTests.class.getResourceAsStream(
        "/migrations/V2__create_regions_table.sql"));

Reader productTableSql = new InputStreamReader(
    DatabaseConnectorTests.class.getResourceAsStream(
        "/migrations/V3__create_products_table.sql"));

this.dc = DatabaseConnector.getConnection();
RunScript.execute(dc, storeTableSql);
RunScript.execute(dc, regionTableSql);
RunScript.execute(dc, productTableSql);

I get the following error:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE PRODUCTS (
  ID INTEGER AUTO_INCREMENT PRIMARY KEY,
  STORE_ID INTEGER NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  REGION_ID INTEGER NOT NULL,
  CREATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UPDATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  DELETED_AT TIMESTAMP,
  UNIQUE(STORE_ID, NAME),
  FOREIGN KEY STORE_FK[*](STORE_ID) REFERENCES STORES(ID),
  FOREIGN KEY REGION_FK(REGION_ID) REFERENCES REGIONS(ID)
) "; expected "("; SQL statement:
CREATE TABLE products (
  id integer AUTO_INCREMENT PRIMARY KEY,
  store_id integer NOT NULL,
  name varchar(255) NOT NULL,
  region_id integer NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at timestamp,
  UNIQUE(store_id, name),
  FOREIGN KEY store_fk(store_id) REFERENCES stores(id),
  FOREIGN KEY region_fk(region_id) REFERENCES regions(id)
) [42001-196]

This code works in MySQL, and I am running H2 in MySQL compatibility mode via the following connection string: jdbc:h2:file:~/database;MODE=MySQL. I'm not sure what else I can do here. This error seems very strange to me, as I have no idea why it would think I need ANOTHER (.

Does anyone know how to fix this? Or have a better suggestion on how to seed my test database? I cannot use hibernate for this task due to a restriction on the system I am running on so I'm forced to do a lot manually.

Thank you!

score 1 · Answer 1 · answered Feb 25 '18 at 22:02

From this post, but with a little more explanation.

Foreign key constraints in H2 do not allowed named constraints for whatever reason. The solution to this problem is to simply remove the name:

CREATE TABLE products (
  id integer AUTO_INCREMENT PRIMARY KEY,
  store_id integer NOT NULL,
  name varchar(255) NOT NULL,
  region_id integer NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at timestamp,
  UNIQUE(store_id, name),
  FOREIGN KEY (store_id) REFERENCES stores(id),
  FOREIGN KEY (region_id) REFERENCES regions(id)
);

The error message is so unclear and misleading. Hopefully this helps someone.

Trouble inserting foreign key with H2

1 Answers1