org.h2.jdbc.JdbcSQLException: Column "ID" not found

Question

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

Answer 1

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.