Hibernate unable to add foreign key constraint

Question

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

Solution 1

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.

Solution 2

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

Comments

[Naanavanalla]

I did that also, but it is still showing same error

[David Pham]

It's so weird. Actually, you just have java entities enough then Hibernate/JPA will create/update tables which maps your entities.

[Naanavanalla]

Actually this is the first time I am facing this issue