Why H2 tries to drop table upon adding a new column with Liquibase changeset?

Question

I'm using in-memory H2 for my Java Spring backend application's tests. I utilize Liquibase for database change managment and Hibernate as ORM framework. My versions are:

• liquibase-core - 4.26.0
• H2 - 2.1.214
• Spring Boot - 3.1.4
• OpenJDK - 17

Recently, I've added the following changeset:

<changeSet author="author" id="0069">
    <addColumn
        schemaName= "schema"
        tableName="table">

        <column name="address_id" type="int" />
    </addColumn>
</changeSet>

It produces the same output for the following changeset:

<changeSet author="author" id="0069">
    <sql>
        ALTER TABLE schema.table ADD address_id INT
    </sql>
</changeSet>

It went smooth with my real PostgreSQL database. However, when it comes to executing @SpringBootTest locally which runs H2 it produces the following error:

Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset liquibase/changelog/000069.xml::0069::author:Reason: liquibase.exception.DatabaseException:Cannot drop "TABLE_COPY_3_186" because "TABLE_COPY_3_186_FKDKH1LTY9WPEGOSJK292PCEH0O, 
TABLE_COPY_3_186_FKEDSHLUMQ8SP6RPM2CP2QAV15V, TABLE_COPY_3_186_FKQ9TRR3YLT6JBKPA99M06Y58JD
...
...
...
depends on it; SQL statement:DROP TABLE "SCHEMA"."TABLE_COPY_3_186" [90107-214] [Failed SQL: (90107) ALTER TABLE schema.table ADD address_id INT]

Error itself does not suprise me as it tries deleting table with existing foreign keys and supposedly recreates it later with the new column. Question is why it tries to do it? I suspect some performance tweaks however it seems irresponsible to introduce such "feature". I'm looking for some elegant solution to that matter. Dropping existing constraints and recreating them later seems like over-engineering. Have you encountered similar issue? How did you resolve it?

It seems that problem occurs since I've upgraded h2 and some other libraries to newer versions.

Any advice would be appreciated. Thank you in advance.

Answer 1

Ultimately I changed the database used for my unit tests to PostgreSQL thanks to Testcontainers.

Now it matches my production database version and type. As you can see in the comments under my initial question this is the right approach to perform your unit tests.

Otherwise, you can encounter issues related to differences between databases and their versions.

Thanks to all participants for the insightful discussion. I consider this question answered.