如何将复合键用作外键？（违反外键约束报错）

我在使用复合键作为外键时遇到了问题。现有数据结构是Country（国家）包含多个City（城市），每个City包含多个District（区县）。没有District表时，City的复合键能正常工作，但添加District表后，插入包含城市和区县的国家条目时触发以下错误：

ERROR: insert or update on table "district" violates foreign key constraint "fk_city_country"
Detail: Key (country_id, city_id)=(city01, country01) is not present in table "city".

相关代码 #

实体类代码 #

CountryEntity.java

// CountryEntity.java
@Entity
@Table(schema = "public", name = "country")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class CountryEntity {
    @Id
    private String id;
 
    @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
    @JoinColumn(name = "country_id")
    private List<CityEntity> cities;
}

CityEntity.java

// CityEntity.java
@Entity
@Table(schema = "public", name = "city")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class CityEntity {
    @EmbeddedId
    private CityKey id;
 
    @OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
    @JoinColumn(name = "country_id")
    @JoinColumn(name = "city_id")
    private List<DistrictEntity> districts;
}

CityKey.java

// CityKey.java
@Embeddable
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class CityKey implements Serializable {
    @Column(name = "country_id")
    private String countryId;
 
    @Column(name = "district_id")
    private String districtId;
}

DistrictEntity.java

// DistrictEntity.java
@Entity
@Table(schema = "public", name = "district")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class DistrictEntity {
    @Id
    public String id;
 
    @Column(name = "country_id")
    private String countryId;
 
    @Column(name = "city_id")
    private String cityId;
}

Liquibase 变更日志 #

# liquibase yaml changelog entry
- changeSet:
    id:  1
    author:  johnDoe
    changes:
      - createTable:
          tableName: country
          columns:
            - column:
                name: id
                type: varchar
                constraints:
                  nullable: false
                  primaryKey: true
      - createTable:
          tableName: city
          columns:
            - column:
                name: country_id
                type: varchar
                constraints:
                  nullable: false
                  references: country(id)
                  foreignKeyName: country_fk
            - column:
                name: city_id
                type: varchar
      - addPrimaryKey:
          columnNames: country_id, city_id
          constraintName: city_cpkey
          tableName: city
      - createTable:
          tableName: district
          columns:
            - column:
                name: id
                type: varchar
                constraints:
                  nullable: false
                  primaryKey: true
            - column:
                name: country_id
                type: varchar
            - column:
                name: city_id
                type: varchar
      - addForeignKeyConstraint:
          baseColumnNames: country_id, city_id
          baseTableName: district
          referencedColumnNames: country_id, city_id
          referencedTableName: city
          constraintName: fk_city_country

问题原因及修复方案 #

1. CityKey 字段定义错误 #

City的复合主键是country_id + city_id，但当前CityKey里误写为district_id，导致实体映射和数据库结构不匹配。修正后代码：

@Embeddable
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class CityKey implements Serializable {
    @Column(name = "country_id")
    private String countryId;
 
    @Column(name = "city_id")
    private String cityId; // 修正字段名
}

2. CityEntity 关联District的注解错误 #

多字段关联时，需用@JoinColumns包裹多个@JoinColumn，明确指定关联的复合键字段：

@OneToMany(cascade = {CascadeType.ALL}, fetch = FetchType.EAGER)
@JoinColumns({
    @JoinColumn(name = "country_id", referencedColumnName = "country_id"),
    @JoinColumn(name = "city_id", referencedColumnName = "city_id")
})
private List<DistrictEntity> districts;

3. DistrictEntity 外键关联优化 #

手动维护country_id和city_id易导致数据不一致，建议用@ManyToOne直接关联City实体：

@Entity
@Table(schema = "public", name = "district")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class DistrictEntity {
    @Id
    public String id;
 
    @ManyToOne
    @JoinColumns({
        @JoinColumn(name = "country_id", referencedColumnName = "country_id"),
        @JoinColumn(name = "city_id", referencedColumnName = "city_id")
    })
    private CityEntity city;
}

4. 插入数据字段顺序验证 #

错误信息显示(country_id, city_id)=(city01, country01)，说明插入时字段顺序颠倒。需确保District的country_id对应国家ID，city_id对应城市ID，与City表复合主键顺序一致。

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