Hibernate H2 specify drop table order

Question

I have pretty much the same problem like this user. Hibernate can't drop the tables of my in-memory test database after each SpringBootTest (e.g. when running mvn test). The desired behavior would be ddl-auto=create-drop, but this doesn't work.

I think the reason could be an invalid order of the DROP TABLE statements, so that Hibernate tries to delete tables on which other tables still depend on.

My data.sql script only contains INSERT statements and the schema is automatically created based on my entities. I tried adding DROP TABLE statements to the top of data.sql and they all pass (ddl-auto=create), because I can specify the order in which they have to be dropped. On the other side, I now have to specify the schema creation in data.sql too..

Is there a way to specify the order of drop statements while not having to specify the schema creation? Or does anyone know a solution for the initial problem?

EDIT:

I want to give an example. I have an User entity that has relationships to other entities (M:N, 1:N, 1:1). When the schema is created, hibernate drops all tables, creates them and adds constrains:

// first test file:
Hibernate: drop table user if exists
... // drop other tables
Hibernate: create table user (username varchar(255) not null, ... , primary key (username))
... // create other tables
Hibernate: alter table X add constraint FKgi38hy0tsrdm332gdjrc0uhm3 foreign key (username) references user
Hibernate: alter table Y add constraint FK5svpy1b71l4jxni0xylrbbdtv foreign key (username) references user
Hibernate: alter table Z add constraint FK5a8fxbb0ug3eo1lisdrrxbbj foreign key (username) references user

// next test file:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Cannot drop "USER" because "FKGI38HY0TSRDM332GDJRC0UHM3, FK5SVPY1B71L4JXNI0XYLRBBDTV, FK5A8FXBB0UG3EO1LISDRRXBBJ" depends on it; SQL statement:
drop table user if exists [90107-200]

This process doesn't work after the first test file, because it violates the constrains. This is why I wanted to specify the drop order.

I don't use CascadeType on my entities, could that cause the problem?

Answer 1

I finally found a work around for my problem. As I said, the errors where caused by trying to drop tables on which other tables still depend on. I thought that this could be related to missing CascadeType specifcations, but I couldn't fix it.

Then I found this answer and it works for me. Additional to my data.sql file (where all my data is inserted to the auto-created schema) I now have a drop-tables.sql where I can specify the correct order of DROP statements. This file is executed before the automatic schema creation and therefore solves my problem.

application.properties:

spring.jpa.properties.javax.persistence.schema-generation.database.action=drop-and-create
spring.jpa.properties.javax.persistence.schema-generation.drop-source=script-then-metadata
spring.jpa.properties.javax.persistence.schema-generation.drop-script-source=drop-tables.sql