hibernate foreign key issue : Error executing DDL "alter table..."

Question

I have a pretty simple object structure that is giving me an error I'm not able to resolve. Have done a bunch of searching, and I think this must be a pretty common use case, so not sure what the issue is. I have these three classes:

@Entity
public class Widget {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int idd;
    private Date date;
    private String type;
    private int ownerId;

    @OneToOne(cascade = CascadeType.ALL)
    private Rating rating;


    @OneToMany(cascade = CascadeType.ALL)
    private List<Tag> tagList;


}


@Entity
public class Rating {
public enum ChartType  {RADAR, BAR, LINE, STAR};

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int ratingId;
private String name;

@Enumerated(EnumType.STRING)
private ChartType chartType;
private double normalizedValue;

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
private List<RatingComponent> componentList = new ArrayList<>();


public Rating() {
    buildNormalizedValue();
}
}


@Entity
public class RatingComponent {


@Id
@GeneratedValue()
private int compId;

private String name;
private double value;
private double maxValue;

}

when saving in the DAO I'm getting errors I don't really understand, such as

2018-07-23 11:43:07,208 WARN o.h.t.s.i.ExceptionHandlerLoggedImpl [main] GenerationTarget encountered exception accepting command : 
Error executing DDL "alter table Rating_RatingComponent drop foreign key FKaudjguwlo1i8tm2jgli5bbnq6" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table Rating_RatingComponent drop foreign key FKaudjguwlo1i8tm2jgli5bbnq6" via JDBC Statement

and further down

2018-07-23 11:43:07,644 WARN o.h.t.s.i.ExceptionHandlerLoggedImpl [main] GenerationTarget encountered exception accepting command : Error executing DDL "create table RatingComponent (compId integer not null, maxValue double precision not null, name varchar(255), value double precision not null, primary key (compId)) engine=InnoDB" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table RatingComponent (compId integer not null, maxValue double precision not null, name varchar(255), value double precision not null, primary key (compId)) engine=InnoDB" via JDBC Statement

I have these properties in my config

    <property name="hibernate.hbm2ddl.auto">create</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

I thought I had gone thru the docs pretty carefully, and think this is a pretty straight forward object design...any ideas as to how to resolve, and where in the docs should I research more carefully to understand the root cause?

thanks!

I dropped the database and re-created, and had the same problem. Also note that I have the 'create' property set, so a new db is created on each run of a unit test — badperson, Jul 23 '18 at 18:01
Works fine for me: `Hibernate: alter table Rating_RatingComponent add constraint FKaudjguwlo1i8tm2jgli5bbnq6 foreign key (componentList_compId) references RatingComponent` Removed the Tag from Widget since you didn't bother to post a Complete, Minimal, Verifiable example — K.Nicholas, Jul 23 '18 at 18:12
I see your output shows a table name of Rating_RatingComponent, wheras mine only shows RatingComponent, and that table does not exist in the db.... — badperson, Jul 23 '18 at 19:53
Your question is about the Rating_RatingComponent table. That's what your error is. It's a join table between Rating and RatingComponent. If you have an existing database structure you should be using auto value validate. — K.Nicholas, Jul 23 '18 at 20:49

score 5 · Answer 1 · answered Sep 20 '20 at 16:15

5

I had used an SQL reserved word 'order' as the entity name, due to which I was getting a similar error. Renaming it solved it for me. Look at your entity names, have you used any reserved word in them?

answered Sep 20 '20 at 16:15

Mohammed Siddiq

477
4
16

I've had a similar issue with a class named "Match". – Johannes Jan 06 '22 at 21:01

score 3 · Answer 2 · answered Mar 18 '19 at 13:08

3

replacing

<property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

by

<property name="hibernate.dialect">org.hibernate.dialect.MySQL8InnoDBDialect</property>

will do the trick

answered Mar 18 '19 at 13:08

Svenmarim

3,633
5
24
56

1

3 years later, you save my ass bro. Thanks! – adflytec Nov 20 '21 at 23:05

score 2 · Answer 3 · answered Mar 10 '20 at 10:11

2

I had to drop-create all the tables to make it work. Also check if you are using any reserved words as your table names.

answered Mar 10 '20 at 10:11

Poçi

243
3
8

score 0 · Answer 4 · answered Mar 10 '20 at 21:32

In my case the DB User didnt have the sufficient permission to alter the table using the reference command. Hint Stack trace had the error like

Caused by: java.sql.SQLSyntaxErrorException: REFERENCES command denied to user

Updating the DB User privileges fixed the issue

Also use the update as ddl-auto

jpa: hibernate: ddl-auto: update

hibernate foreign key issue : Error executing DDL "alter table..."

4 Answers4