Hibernate cannot rebuild tables due to broken foreign keys

Question

Alright so, I have a Spring Boot application (1.5.2.RELEASE) that uses Hibernate (5.0.12.Final) and I'm having a few problems with Hibernate being unable to properly re-create my database on startup.

I configured Hibernate to create the tables again on startup:

spring.jpa.hibernate.ddl-auto=create

Here is my first entity (Client):

@Entity
public class Client {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    private String name;

    @NotNull
    private String firstName;

    @Email
    @NotNull
    private String email;

    @Valid
    @Embedded
    private Credentials credentials = new Credentials();

    @OneToMany(mappedBy = "client")
    private List<Reservation> reservations = new ArrayList<>();

    //Getters, Setters, hashcode & equals omitted
}

Reservation entity:

@Entity
public class Reservation {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    private Client client;

    @Enumerated(EnumType.STRING)
    private State state = State.UNTREATED;
    
    //Getters, Setters, hashcode & equals omitted
}

Here's an overview of the created database:

Here's the console output, I tried to abbreviate it for simplicity's sake:

INFO : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
INFO : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
INFO : HHH000227: Running hbm2ddl schema export

Hibernate: alter table reservation drop foreign key FKoewar6f18rkn4iptr6da4oysv

ERROR : HHH000389: Unsuccessful: alter table reservation drop foreign key FKoewar6f18rkn4iptr6da4oysv
ERROR : Can't DROP 'FKoewar6f18rkn4iptr6da4oysv'; check that column/key exists

Hibernate: drop table if exists client

ERROR : HHH000389: Unsuccessful: drop table if exists client
ERROR : Cannot delete or update a parent row: a foreign key constraint fails

Hibernate: drop table if exists reservation
Hibernate: drop table if exists restaurant
Hibernate: create table client (id bigint not null auto_increment, created_on datetime, credentials_version integer not null, hash varchar(255), username varchar(255), email varchar(255) not null, first_name varchar(255) not null, name varchar(255) not null, primary key (id))

ERROR : HHH000389: Unsuccessful: create table client (id bigint not null auto_increment, created_on datetime, credentials_version integer not null, hash varchar(255), username varchar(255), email varchar(255) not null, first_name varchar(255) not null, name varchar(255) not null, primary key (id))
ERROR : Table 'client' already exists

Hibernate: create table reservation (id bigint not null auto_increment, state varchar(255), client_id bigint, primary key (id))
Hibernate: create table restaurant (id bigint not null auto_increment, primary key (id))
Hibernate: alter table reservation add constraint FKoewar6f18rkn4iptr6da4oysv foreign key (client_id) references client (id)

 INFO  : HHH000230: Schema export complete
 INFO  : Initialized JPA EntityManagerFactory for persistence unit 'default'
 INFO  : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
 INFO  : HHH000227: Running hbm2ddl schema export
ERROR  : HHH000389: Unsuccessful: alter table `Reservation` drop foreign key `FK8gjvlt7rp8i9kp7rdmn107ni7`
ERROR  : Can't DROP 'FK8gjvlt7rp8i9kp7rdmn107ni7'; check that column/key exists
ERROR  : HHH000389: Unsuccessful: drop table if exists `Client`
ERROR  : Cannot delete or update a parent row: a foreign key constraint fails
ERROR  : HHH000389: Unsuccessful: create table `Client` (`id` bigint not null auto_increment, `createdOn` datetime, `credentialsVersion` integer not null, `hash` varchar(255), `username` varchar(255), `email` varchar(255) not null, `firstName` varchar(255) not null, `name` varchar(255) not null, primary key (`id`))
ERROR  : Table 'client' already exists
 INFO  : HHH000230: Schema export complete

I don't know if it matters but the MySQL database version is 5.7.13 running on Windows. Also I didn't include the Restaurant entity and the Credentials embeddable.

I find it strange that Hibernate is trying to drop a foreign key that doesn't exist first and then drop the right one and both fail (if I'm reading it correctly).

What is the reasoning/cause behind this? And how to prevent these issues?

I've looked at other relevant SO questions and they are usually caused by invalid names or simmilar but I don't see anything wrong with my naming. I've also tried prefixing all my tables with "tbl_" and renaming some columns with a prefix as well just in case something was causing a conflict but no luck.

It's bothering me because the database is not updating properly and during active development I don't want to drop/create schema manually every time. Maybe it's worth mentioning that, even if the schema is empty, on startup, hibernate will give errors that things don't exist.

Answer 1

Try it. I think issue in Reservation

@ManyToOne
@JoinColumn(name = "client_id")
private Client client;

Answer 2

Firstly, you should understand how foreign key constraint names are generated:

How does hibernate generate foreign key constraint names?

So, if you change table or foreign key column names, you will have a different foreign key constraint name and Hibernate will not be able to delete the old foreign key constraint.

What you can do:

1. Specify a foreign constraint name using @javax.persistence.ForeignKey annotation (don't confuse it with @org.hibernate.annotations.ForeignKey, although it can be used to).

2. Use spring.jpa.hibernate.ddl-auto = update to just update schema, not to recreate it every time.

3. Use a naming strategy to generate foreign key names (working with Hibernate 5 only). But you will need to think how to combine table, associated table, columns names to have not often foreign constraint name change: Need help in implementing ImplicitNamingStrategy for foreign key column names in Hibernate 5