Hibernate无法添加外键约束 [英] Hibernate unable to add foreign key constraint

问题描述

我已经创建了具有所有外键约束的数据库和所有表.但是当我运行spring boot应用程序时，hibernate抛出错误

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

无法对JDBC目标[更改表]执行架构管理 tlp_client添加约束FKfd2km387c8s4oou769dmw5t94外键 (u_frn_address_id)引用了tlp_address(a_id)]

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
@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 ...
}

用于ClientModel

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

用于AddressModel

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

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

更新 我通过在application.properties中设置spring.jpa.hibernate.ddl-auto=validate来运行应用程序，现在它引发了错误

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

模式验证:在列[a_id]中遇到错误的列类型 表[tlp_address];找到[int(Types#INTEGER)]，但期望 [bigint(Types#BIGINT)]

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

推荐答案

原来的问题似乎随着设置而消失

Seems that the original problem went away with the setting

spring.jpa.hibernate.ddl-auto=validate

新问题是因为hibernate无法更改列类型.无论如何，如果不删除并重新创建列(或整个表)，则-afaik-都是不可能的

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)

您在ClientModel

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

但是在您的创建脚本中

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

要获取Long值以适合列，它应该为bigint，但已将其创建为int.将其更改为bigint

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,

并重新创建表

请参见此和 以供参考.

这篇关于Hibernate无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！