Problems with H2 changing constraint name

Question

I'm trying to set the migration to use on integration tests, but Flyway is not working as expected.

Here's a modified sample of the SQL file to show what I'm doing:

First version:

CREATE TABLE Foobar (
  property1 VARCHAR(999) NOT NULL,
  property2 VARCHAR(999),
  property3 VARCHAR(999),
  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,

  CONSTRAINT Foobar_pk PRIMARY KEY (id)
);

... CREATED OTHER TABLES

Second version:

ALTER TABLE Foobar ADD uuid CHAR(36);
ALTER TABLE Foobar DROP CONSTRAINT Foobar_pk;
ALTER TABLE Foobar DROP COLUMN id;
ALTER TABLE Foobar ADD CONSTRAINT Foobar_pk PRIMARY KEY (uuid)

...

Third version:

--CREATED ANOTHER TABLE

And here's what I'm doing to migrate H2:

  private final String JDBC_H2_CONNECTION_STRING = "jdbc:h2:mem:FooBarDB;MODE=PostgreSQL;DB_CLOSE_DELAY=-1";

  /**
   * Creates the schema of the test database.
   * 
   * @throws SQLException If a SQL error occur.
   */
  @Before
  public void createSchema() throws SQLException {
    final Flyway flyway = new Flyway();

    flyway.setDataSource(JDBC_H2_CONNECTION_STRING, "", "");
    flyway.setInstalledBy(System.getProperty("user.name"));
    flyway.migrate();
  }

  /**
   * Deletes the schema of the test database for posterior use.
   * 
   * @throws SQLException If a SQL error occur.
   */
  @After
  public void deleteSchema() throws SQLException {
    final Flyway flyway = new Flyway();

    flyway.setDataSource(JDBC_H2_CONNECTION_STRING, "", "");
    flyway.clean();
  }

The error I get is:

org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: SQL State  : 90057
Error Code : 90057
Message    : Constraint "Foobar_PK" not found; SQL statement:
ALTER TABLE Foobar DROP CONSTRAINT Foobar_pk; ALTER TABLE Foobar DROP COLUMN id [90057-197]

It seems to be a SQL problem at first, but the weird part is that the migration occurs perfectly when using the Maven plug-in directly with the production database, which uses PostgreSQL, and it gives me this error with H2. So I don't know if it's a problem with H2, or a problem with Flyway (because the integration tests uses the Java version), or if I'm missing something I should do in this special case.

---

After testing the SQL directly on H2 and looking at the catalog, I see something weird with this specific constraint.

Instead of be named as Foobar_pk as clearly specified in CONSTRAINT Foobar_pk PRIMARY KEY (id), it's named CONSTRAINT_F. So I don't think it's a problem with Flyway anymore, and it's just about H2.

Does anyone have a clue about it? Thank you everyone!

Answer 1

This was really a bug on H2, that, based on Katzyn, one of the developers of H2, when a column of an auto-increment type is created it already has an implicit constraint of primary key with a default name. A new commit for this was created and the issue was closed, so I expect for this to be fixed on the next release.

Thank you everyone!