Spring Boot "Cannot add foreign key constraint"

Question

I have a spring boot rest service with the following model classes - Report class -

@Entity
@Table (name = "report")
@EntityListeners(AuditingEntityListener.class)
public class Report {

    @Id
    @Column (name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "from_user_id", nullable = false)
    private Long fromUserId;

    @Column(name = "to_user_id", nullable = false)
    private Long toUserId;

    @Temporal(TemporalType.DATE)
    @JsonFormat(pattern = "dd-MM-yyyy hh:mm:ss")
    @CreatedDate
    private Date createdAt;

    @Column(nullable = false)
    private String observation;

    [ OTHER VARS AND GETTERS AND SETTERS .... ]
}

User class -

@Entity
@Table (name = "user")
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(nullable = false)
    private String firstName;

    @Column(nullable = false)
    private String lastName;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(nullable = false)
    private String password;

    @OneToMany(cascade = CascadeType.ALL, targetEntity = Report.class)
    @JoinColumn(name = "to_user_id")
    private List<Report> reportReceivedList;

    @OneToMany(cascade = CascadeType.ALL, targetEntity = Report.class)
    @JoinColumn(name = "from_user_id")
    private List<Report> reportSentList;

    [GETTERS AND SETTERS .....]
}

This is causing a

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement

which in turn is caused by

Caused by: java.sql.SQLException: Cannot add foreign key constraint

I want to create my mapping such that one user can have multiple sent reports and received reports, and once I delete a user, both sent and received reports should get deleted. I have explored loads of links for creating mappings and I am very confused because of different methods given everywhere.

What specifically should I add in that? I already have a config file which only contains code for exposing Ids of both classes. — StonksMan9000, Jul 12 '18 at 11:03
It seems the problem is in your database structure, not your java code. Does this answer help at all?: https://stackoverflow.com/a/15535110/3415090 — mohammedkhan, Jul 12 '18 at 15:25
@mohammedkhan I have only created a schema in MySQL and the Spring boot code creates the tables when it's run for the first time. How do you recommend I alter my db structure to fix this error? — StonksMan9000, Jul 13 '18 at 07:39

Spring Boot "Cannot add foreign key constraint"

0 Answers0