spring.jpa.hibernate.ddl-auto生成id错误类型引发外键约束异常排查

实体类代码 #

User 实体 #

@Entity
@Table(name = "users", 
    uniqueConstraints = { 
      @UniqueConstraint(columnNames = "username"),
      @UniqueConstraint(columnNames = "email") 
    })
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @NotBlank
  @Size(max = 20)
  private String username;

  @NotBlank
  @Size(max = 50)
  @Email
  private String email;

  @NotBlank
  @Size(max = 120)
  private String password;

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(  name = "user_roles", 
        joinColumns = @JoinColumn(name = "user_id"), 
        inverseJoinColumns = @JoinColumn(name = "role_id"))
  private Set<Role> roles = new HashSet<>();
}

Role 实体 #

@Entity
@Table(name = "roles")
public class Role {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Enumerated(EnumType.STRING)
  @Column(length = 20)
  private ERole name;

  public Role() {

  }
}

运行时异常信息 #

foreign key constraint "fkhfh9dx7w3ubf1co1vdev94g3f" cannot be implemented
  Detail: Key columns "user_id" and "id" are of incompatible types: bigint and character varying.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) ~[postgresql-42.3.6.jar:42.3.6]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286) ~[postgresql-42.3.6.jar:42.3.6]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-4.0.3.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-4.0.3.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) 

问题原因分析 #

这不是Spring Data JPA的bug，核心原因是数据库中已存在的users.id列类型和实体类定义不匹配，导致自动生成的中间表user_roles的user_id类型和它冲突。常见场景包括：

• 手动创建过users表：比如最初直接在数据库里建表时，把id设成了character varying类型，之后用Hibernate的ddl-auto=update模式启动项目时，Hibernate不会主动修改已存在列的类型（避免数据丢失），但会根据实体类的Long id生成user_roles.user_id为bigint，最终引发外键类型不兼容。
• 实体类曾修改过id类型：比如一开始User的id是String类型，Hibernate生成了varchar的id列，后来改成Long但没删表，ddl-auto=update不会把varchar改成bigint，新旧类型冲突。
• 数据库迁移或手动修改过表结构：比如用Flyway、Liquibase等工具做过迁移，或者直接在数据库里修改了users.id的类型，导致和当前实体类定义不一致。

删除表重建后问题解决，是因为Hibernate会完全按照实体类的定义重新生成表：users.id设为bigint（对应Long类型），user_roles.user_id也会同步使用bigint，外键约束自然能正常创建。

内容的提问来源于stack exchange，提问作者Durmaz