Spring Boot JPA外键类型不兼容求助：字段匹配错误

错误信息 #

Error executing DDL "alter table appointment_services modify column appointment_id integer not null" via JDBC [Referencing column 'appointment_id' and referenced column 'id' in foreign key constraint 'FKkv6gwfscv4td54g96ra0p0gn0' are incompatible.] at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:94) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]

相关代码 #

Appointment 实体 #

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class Appointment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long appointmentId;

    @ManyToOne
    @JoinColumn(name = "userId")
    @JsonIgnore
    private User user;

    private String name;
    private String companyName;
    private String email;
    private String phoneNumber;
    private String address;

    @ElementCollection(targetClass = ServiceType.class)
    @CollectionTable(name = "appointment_services", joinColumns = @JoinColumn(name = "appointment_id"))
    @Enumerated(EnumType.STRING)
    @Column(name = "service_type")
    private Set<ServiceType> serviceTypes;

    @Column(name = "additional_service")
    @ElementCollection
    private Set<String> additionalServices;

    @OneToOne(mappedBy = "appointment")
    private Invoice invoice;

    private String appointmentDate;
    private String completionDate;
    private String status;
}

ServiceType 枚举 #

public enum ServiceType {
    COMMERCIAL,
    RESIDENTIAL,
    KITCHEN_CLEANING,
    BATHROOM_CLEANING,
    WINDOW_CLEANING,
    CARPET_CLEANING,
    UPHOLSTERY_CLEANING,
    MOVE_IN_MOVE_OUT,
    POST_CONSTRUCTION,
    EVENT_CLEANUP,
    FLOOR_CLEANING,
    GREEN_CLEANING,
    DEEP_CLEANING,
    REGULAR_MAINTENANCE,
    SANITIZATION_SERVICES,
    PRESSURE_WASHING,
    GARAGE_CLEANING,
    ATTIC_CLEANING,
    LAUNDRY_SERVICES,
    ORGANIZATION_SERVICES
}

问题背景 #

Hibernate通过@ElementCollection自动生成的appointment_services表中，appointment_id列类型与Appointment实体主键appointmentId（对应数据库列id，类型为BIGINT）不匹配，触发外键约束错误。已尝试修改主键列名，但问题未解决，需确保两列类型一致。

• 显式指定外键列类型：在@JoinColumn中通过columnDefinition明确指定列类型为BIGINT NOT NULL，与主键类型对齐。修改两个@ElementCollection字段的注解：

  // 针对serviceTypes字段
  @ElementCollection(targetClass = ServiceType.class)
  @CollectionTable(name = "appointment_services", joinColumns = @JoinColumn(name = "appointment_id", columnDefinition = "BIGINT NOT NULL"))
  @Enumerated(EnumType.STRING)
  @Column(name = "service_type")
  private Set<ServiceType> serviceTypes;
  
  // 针对additionalServices字段
  @Column(name = "additional_service")
  @ElementCollection
  @CollectionTable(joinColumns = @JoinColumn(name = "appointment_id", columnDefinition = "BIGINT NOT NULL"))
  private Set<String> additionalServices;
  

• 重置数据库表结构：若appointment_services表已存在错误结构，Hibernate自动DDL可能无法更新。开发环境下可设置spring.jpa.hibernate.ddl-auto=create让Hibernate重新生成表；生产环境需手动修改列类型或备份数据后重建表。

• 规范主键命名（可选）：当前实体主键字段名appointmentId与列名id不一致，易引发混淆。建议统一命名为id，提升代码可读性：

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id", nullable = false)
  private Long id;
  

• 确认数据库方言配置：确保spring.jpa.properties.hibernate.dialect配置正确（如MySQL 8.x用org.hibernate.dialect.MySQL8Dialect），正确的方言能保证Hibernate生成与数据库兼容的列类型。

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