Using MySQL, why doesn't Hibernate/JPA create foreign key constraints for me?

Question

Documenting this here because I just wasted an hour trying to figure this out.

I have an entity Foo with:

@ManyToOne(optional = false)
@JoinColumn(name = "barId")
private Bar bar;

Why does Hibernate not create a foreign key constraint on foo.bar -> bar.id ?

score 18 · Answer 1 · edited May 23 '17 at 12:32

18

As suggested here Hibernate: Create Mysql InnoDB tables instead of MyISAM you can also change Hibernate dialect to use

org.hibernate.dialect.MySQL5InnoDBDialect

which is less invasive.

Just wanted to add this as an alternative solution.

edited May 23 '17 at 12:32

Community

1
1

answered Sep 07 '12 at 08:59

konqi

5,137
3
34
52

I'm using JPA to create my schema, where is the above placed? in the persistance.xml? – Ammar Samater Mar 02 '18 at 05:57
it goes in the hibernate.properties file – konqi Mar 02 '18 at 09:55

score 14 · Accepted Answer · answered Jan 04 '12 at 00:33

14

It's because MySQL does not support foreign key constraints on tables created with ENGINE=MyISAM. You need to create (both!) tables with ENGINE=InnoDB. You can do so by tweaking my.cnf, and adding a default, or by using a special variable in your JDBC URL:

jdbc:mysql://localhost/dbname?characterEncoding=utf8&sessionVariables=storage_engine=InnoDB

answered Jan 04 '12 at 00:33

George Armhold

30,824
50
153
232

1

FWIW, in MySQL 5.5, InnoDB is *finally* the default storage engine out of the box. – Bill Karwin Jan 04 '12 at 00:44
I've added this to my JDBC connection string and now I get `java.sql.SQLException: Unknown system variable 'storage_engine'` – naXa stands with Ukraine Oct 23 '18 at 07:21
I've added this to my JDBC connection string and now I get java.sql.SQLException: Unknown system variable 'storage_engine' – TanvirChowdhury Apr 16 '19 at 12:06

score 10 · Answer 3 · edited Jun 07 '18 at 20:09

10

With SpringBoot you can set application.properties to:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

and it should work fine.

edited Jun 07 '18 at 20:09

Milo

3,365
9
30
44

answered Jun 07 '18 at 18:21

Thiago Matar

111
1
3

this solved my problem while working with Spring Boot. – jagzviruz Aug 24 '18 at 17:30
If this doesn't work, try this: spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect – Prashant Saraswat Mar 10 '19 at 23:05
This actually worked for me spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect – AKS Sep 28 '19 at 13:01

Using MySQL, why doesn't Hibernate/JPA create foreign key constraints for me?

3 Answers3

Linked