Spring集成PostgreSQL时自动生成多余关联列的解决方法

运行Spring应用后，PostgreSQL中的USERS表被自动生成了额外的DEPARTMENT_DEPARTMENT_ID和ORGANIZATION_ORGANIZATION_ID列，原本预期的表结构仅包含USER_ID、DEPARTMENT_ID、ORGANIZATION_ID。相关代码与表结构如下：

User.java类代码 #

public static final String TABLE_NAME = "USERS";
public static final String SEQENCE = "USERS_SEQ";

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQENCE)
@SequenceGenerator(name = SEQENCE, sequenceName = SEQENCE, allocationSize = 1)
private Long userId;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "department_id")
private Department department;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "organizazion_id")
private Organization organization;

USERS表建表脚本 #

CREATE TABLE IF NOT EXISTS USERS (
    USER_ID BIGINT NOT NULL, 
    DEPARTMENT_ID BIGINT,
    ORGANIZATION_ID BIGINT,
    PRIMARY KEY (USER_ID),
    FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID),
    FOREIGN KEY (ORGANIZATION_ID) REFERENCES ORGANIZATION(ORGANIZATION_ID)
);

Department.java类核心代码 #

public static final String TABLE_NAME = "DEPARTMENT";
public static final String SEQENCE = "DEPARTMENT_SEQ";

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQENCE)
@SequenceGenerator(name = SEQENCE, sequenceName = SEQENCE, allocationSize = 1)
private Long departmentId;
private String departmentName;
// 其他基础类型字段

DEPARTMENT表建表脚本 #

CREATE TABLE IF NOT EXISTS DEPARTMENT (
    DEPARTMENT_ID BIGINT NOT NULL, 
    DEPARTMENT_NAME VARCHAR(50),
    PRIMARY KEY (DEPARTMENT_ID)
);

Organization.java类核心代码 #

public static final String TABLE_NAME = "ORGANIZATION";
public static final String SEQENCE = "ORGANIZATION_SEQ";

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = SEQENCE)
@SequenceGenerator(name = SEQENCE, sequenceName = SEQENCE, allocationSize = 1)
private Long organizationId;
private String organizationName;
private String street;
private String postalCode;
private String state;
private String city;

ORGANIZATION表建表脚本 #

CREATE TABLE IF NOT EXISTS ORGANIZATION (
    ORGANIZATION_ID BIGINT NOT NULL, 
    ORGANIZATION_NAME VARCHAR(50),
    STREET VARCHAR(255),
    POSTAL_CODE VARCHAR(50),
    STATE VARCHAR(255),
    CITY VARCHAR(255),
    PRIMARY KEY (ORGANIZATION_ID)
);

要求：移除多余的DEPARTMENT_DEPARTMENT_ID和ORGANIZATION_ORGANIZATION_ID列，且不能使用@Column(name = "column_name")注解处理。

1. 修正拼写错误（核心问题） #

观察User类中Organization的关联配置，发现@JoinColumn的name属性拼写错误：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "organizazion_id") // 多了一个字母z
private Organization organization;

数据库中对应列名为ORGANIZATION_ID，注解中的错误导致Hibernate无法匹配现有列，自动生成了默认外键列ORGANIZATION_ORGANIZATION_ID。

修正后代码：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "organization_id")
private Organization organization;

2. 检查双向关联配置（解决DEPARTMENT_DEPARTMENT_ID问题） #

如果Department类中存在@OneToMany关联User的配置，必须指定mappedBy属性，否则Hibernate会将其视为独立的单向关联，自动生成额外外键列。

错误示例（无mappedBy）：

@OneToMany
private List<User> users;

正确配置：

@OneToMany(mappedBy = "department") // 指向User类中的department字段
private List<User> users;

同理，若Organization类中有@OneToMany关联User，需添加mappedBy = "organization"。

3. 清理并验证 #

• 手动删除USERS表中的DEPARTMENT_DEPARTMENT_ID和ORGANIZATION_ORGANIZATION_ID列，或在开发环境中重新初始化数据库。
• 重启应用，检查USERS表结构，确认多余列不再生成。

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