使用Spring Data JDBC向PostgreSQL保存一对多实体时遇外键错误

问题场景 #

使用Spring Boot + Spring Data JDBC向PostgreSQL保存一对多关联实体（Season与Episode），执行保存操作时触发外键约束异常，推测Spring无法读取Season自动生成的主键值。

主应用代码 #

@SpringBootApplication
public class TvApplication
{
    @Autowired
    private SeasonRepository seasonRepository;
    @EventListener(ApplicationReadyEvent.class)
    public void saveToDatabase()
    {
        Episode episode1 = new Episode("firstEpisode");
        Episode episode2 = new Episode("secondEpisode");
        Season season = new Season("firstSeason");
        season.addEpisode(episode1);
        season.addEpisode(episode2);
        seasonRepository.save(season);
    }
}

Season实体类 #

@Table("season")
public class Season
{
    @Id
    @Column("id")
    private long id;
    @Column("name")
    private String name;
    @MappedCollection(idColumn = "id", keyColumn = "seasonid")
    private List<Episode> episodes;
    public Season(long id, String name, List<Episode> episodes)
    {
        this.id = id;
        this.name = name;
        this.episodes = episodes;
    }
    public Season(String name)
    {
        this(0, name, new ArrayList<>());
    }
    public void addEpisode(Episode episode)
    {
        episodes.add(episode);
    }
}

Episode实体类 #

@Table("episode")
public class Episode
{
    @Id()
    @Column("id")
    private long id;
    @Column("name")
    private String name;
    public Episode(long id, String name)
    {
        this.id = id;
        this.name = name;
    }
    public Episode(String name)
    {
        this(0, name);
    }
}

触发的异常信息 #

org.postgresql.util.PSQLException: ERROR: insert or update on table "episode" violates foreign key constraint "episode_seasonid_fkey"
Detail: Key (seasonid)=(0) is not present in table "season"

问题原因与修复方案 #

核心原因 #

1. 主键生成策略缺失：Season的id初始值为0，未配置自增主键策略，Spring Data JDBC无法获取PostgreSQL自动生成的真实主键，导致关联Episode时使用无效的0作为外键值。
2. @MappedCollection参数配置错误：注解中的idColumn与keyColumn参数颠倒，无法正确映射父实体主键到子实体的外键字段。

具体修复步骤 #

1. 配置Season主键自增
   在Season的@Id字段添加@GeneratedValue注解，指定PostgreSQL的自增策略：

   @Id
   @Column("id")
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;
   

   同时确保数据库season表的id字段为自增类型：

   CREATE TABLE season (
       id BIGSERIAL PRIMARY KEY,
       name VARCHAR(255) NOT NULL
   );
   

2. 修正@MappedCollection注解参数
   调整Season中集合字段的注解参数，明确父主键与子外键的映射关系：

   @MappedCollection(idColumn = "seasonid", keyColumn = "id")
   private List<Episode> episodes;
   

   • idColumn：Episode表中存储Season外键的列名（即seasonid）
   • keyColumn：Season表的主键列名（即id）

3. 确保Episode表外键约束正确
   数据库中Episode表需正确定义外键关联：

   CREATE TABLE episode (
       id BIGSERIAL PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       seasonid BIGINT REFERENCES season(id)
   );
   

完成以上调整后，Spring Data JDBC会在保存Season时自动回填数据库生成的主键值，关联的Episode将使用正确的外键值执行保存，外键约束异常即可解决。

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