Spring Hibernate MySql - Cannot add foreign key constraint

Question

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...)

Is idInterruttore a primary key? If do why do you use uniqueConstraints = @UniqueConstraint(columnNames = "id_interruttore")? How do you bootstrap Hibernate (update schema, create, do nothing)? — v.ladynev
– v.ladynev, Commented May 5, 2016 at 14:04
I do nothing with hibernate, the DB is already there, i just connect to it. I connect with SpringBoot default process — MarioC
– MarioC, Commented May 6, 2016 at 16:50
Looks like, Hibernate can't add FK_4kmtr3q9e2hnaoutsxgahhm63, because of your schema already has such constraint with other name. — v.ladynev
– v.ladynev, Commented May 10, 2016 at 13:19
@besmart, Did you manage to get the code working? I am facing the same problem. Any help is much appreciated. — Vijay Kalidindi
– Vijay Kalidindi, Commented Jan 24, 2017 at 6:13

Gus · Accepted Answer · 2017-03-04 19:32:58Z

6

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.

answered Mar 4, 2017 at 19:32

Gus

16.1k7 gold badges28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ranjesh · Accepted Answer · 2019-02-11 22:17:59Z

4

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

answered Feb 11, 2019 at 22:17

ranjesh

711 bronze badge

1 Comment

Vihanga Yasith Over a year ago

inspired by this answer, in my case I added this line spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

SHIV KUMAR · Accepted Answer · 2023-03-13 15:16:53Z

0

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.

answered Mar 13, 2023 at 15:16

SHIV KUMAR

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Spring Hibernate MySql - Cannot add foreign key constraint

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related