org.h2.jdbc.JdbcSQLException：列“ID”未找到 [英] org.h2.jdbc.JdbcSQLException: Column "ID" not found

问题描述

我的代码中有以下DDL：

  CREATE TABLE如果不存在SOMETABLE（
 id BIGINT AUTO_INCREMENT NOT NULL，
 ... 
 FOREIGN KEY（id）REFERENCES OTHERTABLE（id）
 ... 
）; 
  

以下是OTHERTABLE的定义：

  create tableOTHERTABLE（
idBIGINT由默认值作为IDENTITY生成（START WITH 1）NOT NULL PRIMARY KEY，
codeVARCHAR NOT NULL，
nameVARCHAR NOT NULL，
enabledBOOLEAN NOT NULL，
app_idVARCHAR NOT NULL）; 
 

请注意，OTHERTABLE是由SLICK（Scala ORM Stack）自动生成的！

这适用于MySQL（这是我们的dev / prod数据库），但是，我们的单元测试使用H2数据库，并执行这个堆栈跟踪：

  org.h2.jdbc.JdbcSQLException：未找到列ID
 at org.h2.message.DbException.getJdbcSQLException（DbException.java:329） 
 at org.h2.message.DbException.get（DbException.java:169）
 at org.h2.message.DbException.get（DbException.java:146）
 at org.h2 .table.Table.getColumn（Table.java:613）
在org.h2.table.IndexColumn.mapColumns（IndexColumn.java:75）
 at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate （AlterTableAddConstraint.java:203）
 at org.h2.command.ddl.AlterTableAddConstraint.update（AlterTableAddConstraint.java:70）
 at org.h2.command.ddl.CreateTable.update（CreateTable.java ：169）
 at org.h2.command.CommandContainer.update（CommandContainer.java:79）
 at org.h2.command.Command.executeUpdate（Command.java:235）
  

有一种方法，即在所有地方将id更改为id

由于MySQL是我们的prod数据库，我别无选择，只能忽略单元测试。

$

感谢

解决方案

如果在创建 OTHERTABLE 时使用双引号引用 id c $ c>id），那么在创建参照完整性约束以及查询数据时，还必须引用它。基本上，你必须引用它每次。我建议在创建表时不要引用它，因为这样你不必在以后引用它。引用表示标识符区分大小写。对于MySQL，它的工作原理，因为内部MySQL将unquotes标识符转换为小写，不像其他数据库。但对于H2和其他数据库，它不起作用。

以下两个语句适用于MySQL和H2：

 如果不存在其他表，则CREATE TABLE（
 id BIGINT AUTO_INCREMENT NOT NULL 
）; 
如果不存在SOMETABLE，则创建表（
 id BIGINT AUTO_INCREMENT NOT NULL，
 FOREIGN KEY（id）REFERENCES OTHERTABLE（id）
）; 
  

所以如果你在第二个语句中有一个异常，你很有可能使用不同的方式创建第一个表（ OTHERTABLE ）。这是问题所在。

下一次，如果您提出问题，请同时包含 create table 第一个表格的语句，并张贴完成错误讯息。

I have the following DDL in my code:

CREATE TABLE IF NOT EXISTS SOMETABLE (
  id BIGINT AUTO_INCREMENT NOT NULL,
  ...
  FOREIGN KEY (id) REFERENCES OTHERTABLE(id)
  ...
);

Here's the definition of OTHERTABLE:

create table "OTHERTABLE" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "code" VARCHAR NOT NULL,
  "name" VARCHAR NOT NULL,
  "enabled" BOOLEAN NOT NULL,
  "app_id" VARCHAR NOT NULL);

Please note that OTHERTABLE is auto generated by SLICK (Scala ORM Stack)!

This works with MySQL (which is our dev/prod database), however, our unit tests use H2 database, and executing this gives the following stack trace:

org.h2.jdbc.JdbcSQLException: Column "ID" not found
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.table.Table.getColumn(Table.java:613)
at org.h2.table.IndexColumn.mapColumns(IndexColumn.java:75)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:203)
at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:70)
at org.h2.command.ddl.CreateTable.update(CreateTable.java:169)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:235)

There is a way out of this, and that is to change id to "id" in all the places, but this changes breaks in MySQL!!

Since MySQL is our prod database, I have no other choice but to ignore the unit-tests!!

Is there a solution for this on the H2 database side?

Thanks

解决方案

If you quote the column id when creating OTHERTABLE using double quotes ("id"), then you have to quote it as well when creating the referential integrity constraint, and when querying data. Basically, you have to quote it each time. I suggest to not quote it when creating the table, because that way you don't have to quote it later on. Quoting means the the identifier is case sensitive. For MySQL, it works because internally MySQL converts unquotes identifiers to lowercase, unlike other databases. But for H2 and other databases it doesn't work.

The following two statements work for both MySQL and H2:

CREATE TABLE IF NOT EXISTS OTHERTABLE (
  id BIGINT AUTO_INCREMENT NOT NULL
);
CREATE TABLE IF NOT EXISTS SOMETABLE (
  id BIGINT AUTO_INCREMENT NOT NULL,
  FOREIGN KEY (id) REFERENCES OTHERTABLE(id)
);

So if you got an exception in the second statement, you most likely have used a different way to create the first table (OTHERTABLE). And this is where the problem is.

Next time, if you ask a question, please also include the create table statement of the first table, and post the complete error message.

这篇关于org.h2.jdbc.JdbcSQLException：列“ID”未找到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！