用H2插入外键的麻烦

Question

我正在使用H2测试数据库代码。为此，我首先阅读一组SQL文件，然后使用RunScript.execute执行SQL。

我的真正数据库是mySQL，我已经测试了SQL脚本以确保它们工作并且可以。这个问题绝对是H2的内部。这是一组脚本：

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)
);

运行这3个脚本以使用MySQL Workbench创建表格。通过以下代码从Junit运行它们：

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);

我有以下错误：

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]

此代码在MySQL中起作用，我通过以下连接字符串：jdbc:h2:file:~/database;MODE=MySQL在MySQL兼容模式下运行H2。我不确定我还能在这里做什么。这个错误对我来说似乎很奇怪，因为我不知道为什么它会认为我需要另一个(。

有人知道如何解决这个问题吗？还是关于如何播种我的测试数据库有更好的建议？由于对正在运行的系统的限制，我无法将Hibernate用于此任务，因此我被迫手动做很多事情。

谢谢！

Answer 1

来自这篇文章，但有更多的解释。

H2中的外键约束不允许出于任何原因命名约束。解决此问题的解决方案是简单地删除名称：

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)
);

错误消息不清楚和误导。希望这对某人有帮助。