CODEWITHSUNDEEP
hibernatejpaspring-boot
Naanavanalla
Naanavanalla

Reputation: 1522

Hibernate unable to add foreign key constraint

I have already created database and all tables with all foreign key constraints. But when I run the spring boot application hibernate is throwing error

Unable to execute schema management to JDBC target [alter table tlp_client add constraint FKfd2km387c8s4oou769dmw5t94 foreign key (u_frn_address_id) references tlp_address (a_id)]

Entity

@Entity
@Table(name = "tlp_client")
public class ClientModel {

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

   @Column(name = "c_name")
   private String name;

   @Column(name = "c_description")
   private String description;

   @Column(name = "c_web_url")
   private String webUrl;

   @Column(name = "c_created_at")
   private Calendar createdAt;

   @Column(name = "c_is_active")
   private Boolean isActive;

   @OneToOne
   @JoinColumn(name = "u_frn_created_by", referencedColumnName = "u_id")
   private UserModel createdBy;

   @OneToOne
   @JoinColumn(name = "u_frn_address_id", referencedColumnName = "a_id")
   private AddressModel address;

    }
   // getters and setters ...
}

SQL for ClientModel

create table tlp_client (
   c_id INT(11) AUTO_INCREMENT,
   c_name varchar(255) NOT NULL,
   c_description varchar(255), 
   c_web_url varchar(255),
   c_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   c_is_active TINYINT(1) DEFAULT 1,

   c_frn_created_by INT(11),
   c_frn_address_id INT(11),

   PRIMARY KEY (c_id),
   FOREIGN KEY (c_frn_address_id) REFERENCES tlp_address (a_id),
   FOREIGN KEY (c_frn_created_by) REFERENCES tlp_user (u_id)
);

SQL forAddressModel

create table tlp_address (
   a_id INT(11) AUTO_INCREMENT,
   a_address varchar(255),
   a_city varchar(255),
   a_state varchar(255),
   a_country varchar(255),
   a_zip varchar(8),

   PRIMARY KEY (a_id)
);

My Question is, I have already created all the tables and still why hibernate is trying to create tables?

application.properties

spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/dbName?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.pool.size=20
server.port=8090

UPDATE I ran the application by setting spring.jpa.hibernate.ddl-auto=validate in application.properties, now it is throwing the error

Schema-validation: wrong column type encountered in column [a_id] in table [tlp_address]; found [int (Types#INTEGER)], but expecting [bigint (Types#BIGINT)]

Upvotes: 2

Views: 21484

Answers (4)

Ghasem Sadeghi
Ghasem Sadeghi

Reputation: 1854

This can have many reasons.
For example, in the MYSQL database, you can use the link below : enter link description here

One of the common reasons that may occur in database development is that the type of columns in the code and the database are not the same.
For example :
In your entity, "id" field might result in "int(11)",but the current database expect "bigint(20)".

This interference will result in the following error :

Hibernate unable to add foreign key constraint

Finally check step by step your primary-foreign keys.
I hope this will help you...

Upvotes: 0

Naanavanalla
Naanavanalla

Reputation: 1522

The error was due to a simple mistake I made. In the sql scripts, I have field c_frn_addres_id, but in the entity class, I was mapping it to u_frn_address_id. So after changing u_frn_address_id to c_frn_address_id, it is working fine

Upvotes: 1

David Pham
David Pham

Reputation: 1803

"Hibernate is trying to create tables", because do the configuration with the proeprty spring.jpa.hibernate.ddl-auto. Let change it's value like this

spring.jpa.hibernate.ddl-auto=update

You also refer to the working example Spring Boot JPA One to Many Relationship Mapping

Upvotes: 0

pirho
pirho

Reputation: 12215

Seems that the original problem went away with the setting

spring.jpa.hibernate.ddl-auto=validate

The new problem is because hibernate is unable to change column type. It is not possible -afaik- anyway without dropping and re-creating the column (or whole table)

You have in your ClientModel

@Column(name = "c_id")
private Long id; // Long maps to bigint 8 bytes

However in your create script you have

c_id INT(11) AUTO_INCREMENT, -- int is 4 bytes

To get Long value to fit in the column it should be bigint but you have created it as int. Change it to bigint

c_id BIGINT AUTO_INCREMENT,

and re-create table

See this and this for reference.

Upvotes: 2

Related Questions