CODEWITHSUNDEEP
mysqlspringhibernate
MarioC
MarioC

Reputation: 3228

Spring Hibernate MySql - Cannot add foreign key constraint

I have a running Spring Boot application with Mysql and Hibernate.

While launching it, i'm getting this error

Unsuccessful: alter table SMARTPARK.illuminazione add constraint FK_4kmtr3q9e2hnaoutsxgahhm63 foreign key (id_interruttore) references SMARTPARK.interruttori (id_interruttore)
2016-05-05 08:46:35 ERROR SchemaUpdate:262 - Cannot add foreign key constraint

I have two table/entities

Illuminazione.java is (just the interesting parts...)

@Entity
@Table(name = "illuminazione", catalog = "SMARTPARK")
public class Illuminazione {
    private int idilluminazione;
    private Interruttore interruttore;
    private Date dateTime;
    private Date lastDateTime;
    private boolean isLit;

    @ManyToOne
    @JoinColumn(name = "id_interruttore")
    public Interruttore getInterruttore() {
        return this.interruttore;
    }

    public void setInterruttore(Interruttore interruttore) {
        this.interruttore = interruttore;
    }

In Interruttore.java I have the @OneToMany relation with Illuminazione

@Entity
@Table(name = "interruttori", catalog = "SMARTPARK", uniqueConstraints = @UniqueConstraint(columnNames = "id_interruttore"))
public class Interruttore implements java.io.Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private int idInterruttore;
    private int numeroInterruttore;
    private String nomeInterruttore;
    private String descrizione;
    private List<Luce> luci;
    private GpioController gpio;
    private GpioPinDigitalOutput relePin;
    private Pin pin;
    private boolean remoto;
    private boolean stato;
    private Date dateTime;
    private Set<Illuminazione> illuminazione;

@OneToMany(fetch = FetchType.LAZY, mappedBy = "interruttore")
    public Set<Illuminazione> getIlluminazione() {
        return illuminazione;
    }

    public void setIlluminazione(Set<Illuminazione> illuminazione) {
        this.illuminazione = illuminazione;
    }

Every time I start the application, during the boot i'm getting this error (even if the application seems working good...)

Upvotes: 2

Views: 9821

Answers (3)

SHIV KUMAR
SHIV KUMAR

Reputation: 1

I faced this issue when I have two entity one is Order_Details and other is Order_Items. Below is my entity

@Entity
@Table(name="order_details")
public class OrderDetails {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="order_id")
    private Integer orderId;
    @Column
    private String userEmail;
    @Column
    private Double orderValue;
    @Column
    @CreatedDate
    private Timestamp createDate;
    @Column
    @LastModifiedDate
    private Timestamp updateDate;
    @Column
    private Timestamp deliveryDate;
@OneToMany(mappedBy="orderDetails",cascadeCascadeType.ALL,fetch=FetchType.EAGE)
    @JsonManagedReference
    private Set<OrderItems> orderItems;
}

@Entity
@Table(name="order_items")
public class OrderItems {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="order_item_id")
    private Integer orderItemId;
    @Column
    private Integer productId;
    @Column
    private Integer quantity;
    @Column
    private Integer isDelete;
    @JoinColumn(name="order_id",nullable=false)// error line
    @JsonBackReference
    @ManyToOne
    private OrderDetails orderDetails;
}

Problem was @JoinColumn(name="order",
when I changed the name="order_id" it works, 
because order is a keyword in sql, we cannot use it as table name or column name.

Upvotes: 0

ranjesh
ranjesh

Reputation: 71

I hit similar problem . To me apparently hibernate/Spring was NOT using mysql Engine -INNODB , you need engine INNODB for mysql to generate foreign key constraint. Using the following properties in application.properties, makes spring boot/hibernate to use mysql engine INNODB. So foreign key constraint works and hence also delete cascade 

spring.jpa.hibernate.use-new-id-generator-mappings=true
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

Upvotes: 4

Gus
Gus

Reputation: 16017

I had the same error message, which I found out was caused by incorrect annotations. Hibernate was trying to run

alter table cidades 
    add constraint FKdt0b3ronwpi1upsrhaeq6r69n 
    foreign key (estado_id) 
    references estados (id)

And when I looked at my Cidade.java, I found this mapping

@ManyToOne
@JoinColumn(name = "cidade_id")
private Estado estado;

The error was in "cidade_id", which should have been "estado_id". It would be great if @besmart could provide the DB table info, since the error could be caused by a typo (e.g. id_interruttore could actually be id_interruttori).

I hope this helps someone in the future.

Upvotes: 6

Related Questions