Error executing DDL "alter table score add constraint foreign key (enrollment_id) references enrollment (id)" via JDBC Statement

Question

I have an Enrollment class which has list of scores. I am trying to make a reference to the Score class but getting the following:

Error executing DDL "alter table score add constraint ... foreign key (enrollment_id) references enrollment (id)" via JDBC Statement Caused by: java.sql.SQLException: Failed to open the referenced table 'enrollment'

After running the application, the Score table is created in the database, but the Enrollment table is missing and enrollment_id column of the Score is not a foreign key.

How can I solve this problem? I have tried to annotated with @Cache and to ignore the double creation of the table but it was not successfull. I am using MySQL.

@Entity
@Table(name = "enrollment")
public class Enrollment {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public int id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "student_id")
    public Student student;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "date", nullable = false)
    public Date date;

    @Column(name = "rank", nullable = false)
    public int rank;
    
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "specialty_id", referencedColumnName = "id")
    public Specialty specialty;
    
    @Lob
    @Basic(fetch = FetchType.LAZY)
    @Column(name = "files", columnDefinition = "BLOB", nullable = false)
    private byte[] files;
    
    @OneToMany(mappedBy = "enrollment", fetch = FetchType.LAZY)
    public List<Score> scores;
    
    @Column(name = "state", length = 50, nullable = false)
    public String state;
   
}

@Entity
@Table(name = "score")
public class Score {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "score_forming_object_id", referencedColumnName = "id")
    private ScoreFormingObject scoreFormingObject;
    
    @Column(name = "score", nullable = false)
    private double score;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "enrollment_id")
    private Enrollment enrollment;
}

Answer 1

https://stackoverflow.com/a/64316793/14644191 @Rajib Garai's answer worked for me. I have added spring.jpa.properties.hibernate.globally_quoted_identifiers=true to application.properties and the issue was resolved.

Answer 2

spring.jpa.hibernate.ddl-auto=update

spring.jpa.database-platform=org.hibernate.dialect.MySQL55Dialect

adding these 2 to my application.properties solved my issue

Answer 3

Check if your Enrollment entity class is in the same or in a sub-directory of the application class.

Answer 4

I had been struggling with the same problems later on it works for me when change the table name which was a reserved word in MySQL DB, So make sure when you create a table in mysql db it shouldn't be a reserved word.