Spring Boot JPA同表外键约束错误：保存报表时冲突

在Spring Boot JPA项目中，保存Project对象后尝试保存Report时触发外键约束冲突错误。排查发现projectv2表存在错误的自引用外键约束（自身id字段关联自身id），而正确的关联逻辑应该是reports表的project_id字段关联projectv2表的id字段。

报错信息 #

2023-04-01 15:31:58.246  WARN 59052 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 547, SQLState: 23000
2023-04-01 15:31:58.247 ERROR 59052 --- [nio-8080-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : The INSERT statement conflicted with the FOREIGN KEY constraint "FKrqfhc8qp99ymovwd852w5mx44". The conflict occurred in database "targetally", table "dbo.projectv2", column 'id'.
2023-04-01 15:31:58.838 ERROR 59052 --- [nio-8080-exec-5] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "FKrqfhc8qp99ymovwd852w5mx44". The conflict occurred in database "targetally", table "dbo.projectv2", column 'id'.

相关实体类代码 #

ProjectV2 实体 #

@Table(name = "projectv2")
@EntityListeners(AuditingEntityListener.class)
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ProjectV2 implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  private String id;

  private String name;

  @Column(name="categories")
  @ElementCollection(targetClass=String.class)
  private List<String> categories;

  @Enumerated(EnumType.STRING)
  private ThemeEnum theme;

  @Enumerated(EnumType.STRING)
  private ProjectScopeEnum projectScope;

  @CreatedDate
  private LocalDateTime creationDate;

  @LastModifiedDate
  private LocalDateTime lastUpdatedAt;

  @JsonManagedReference(value = "reports")
  @OneToMany(mappedBy = "project", cascade = CascadeType.ALL,orphanRemoval = true, fetch = FetchType.LAZY)
  private List<ReportV2> reports= new ArrayList<>();

  @JsonManagedReference(value = "userAccess")
  @OneToMany(mappedBy = "projectAccess", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  private List<EntityAccessV2> userAccess;
}

ReportV2 实体 #

@Entity
@Table(name = "reports")
@EntityListeners(AuditingEntityListener.class)
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ReportV2 implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  private String id;

  @JsonProperty("projectId")
  private String project_id;

  private String name;

  @ElementCollection(targetClass = String.class)
  private List<String> categories;

  @Convert(converter = LayoutConverterJson.class)
  private Layout layout;

  @Enumerated(EnumType.STRING)
  private StateEnum state = StateEnum.DRAFT;

  private String reportSnapshotUrl;

  //private List<SurveyMetadata> surveyMetadataList;

  @CreatedDate
  private LocalDateTime createdAt;

  @LastModifiedDate
  private LocalDateTime updatedAt;

  @JsonBackReference(value = "project")
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "project_id", referencedColumnName = "id", insertable = false, updatable = false)
  private ProjectV2 project;

  @JsonManagedReference(value = "userAccess")
  @OneToMany(mappedBy = "reportAccess", cascade = CascadeType.ALL)
  private List<EntityAccessV2> userAccess;
}

EntityAccessV2 实体 #

@Entity
@Table(name = "entity_access")
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class EntityAccessV2 {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @Enumerated(EnumType.STRING)
  private EntityType entityType;

  private String entityId;

  @Enumerated(EnumType.STRING)
  private UserRoleEnum userRole;

  @Enumerated(EnumType.STRING)
  private AccessTypeEnum accessType;

  private String value;

  private String username;

  private String email;

  @JsonBackReference(value = "projectAccess")
  @ManyToOne(fetch = FetchType.LAZY)
  @Where(clause = "entityType = 'PROJECT'")
  @JoinColumn(name = "entityId", referencedColumnName = "id", insertable = false,updatable = false )
  private ProjectV2 projectAccess;

  @JsonBackReference(value = "reportAccess")
  @ManyToOne(fetch = FetchType.LAZY)
  @Where(clause = "entityType = 'REPORT'")
  @JoinColumn(name = "entityId", referencedColumnName = "id",insertable = false,updatable = false)
  private ReportV2 reportAccess;
}

数据库表结构（Azure SQL） #

projectv2表存在错误的自引用外键约束，建表语句如下：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[projectv2](
    [id] [varchar](255) NOT NULL,
    [creation_date] [datetime2](7) NULL,
    [last_updated_at] [datetime2](7) NULL,
    [name] [varchar](255) NULL,
    [project_scope] [varchar](255) NULL,
    [theme] [varchar](255) NULL,
    [categories] [varchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[projectv2] ADD PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [dbo].[projectv2]  WITH CHECK ADD  CONSTRAINT [FK268hsfk4bsht8jfpbfw0hnpd3] FOREIGN KEY([id])
REFERENCES [dbo].[projectv2] ([id])
GO
ALTER TABLE [dbo].[projectv2] CHECK CONSTRAINT [FK268hsfk4bsht8jfpbfw0hnpd3]
GO

解决方案 #

1. 移除错误的自引用外键约束 #

在Azure SQL中执行SQL语句，删除projectv2表上的错误自引用外键：

ALTER TABLE [dbo].[projectv2] DROP CONSTRAINT [FK268hsfk4bsht8jfpbfw0hnpd3]
GO

2. 修正实体类关联逻辑 #

当前ReportV2实体同时维护了project_id字符串字段和project关联对象，且@JoinColumn设置了insertable = false, updatable = false，导致JPA无法自动维护外键关联。建议调整为：

• 删除手动维护的project_id字段，由JPA通过关联对象自动管理外键
• 或保留project_id，但修改@JoinColumn属性，并确保保存时关联对象正确设置

调整后的ReportV2核心代码示例：

@Entity
@Table(name = "reports")
// 其他注解不变
public class ReportV2 implements Serializable {
  // 其他字段不变
  // 移除手动的project_id字段
  @JsonBackReference(value = "project")
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "project_id", referencedColumnName = "id") // 移除insertable和updatable=false
  private ProjectV2 project;
  // 其他代码不变
}

3. 确保保存Report时关联正确的Project对象 #

业务代码中保存Report时，需将已持久化的ProjectV2对象设置到ReportV2的project属性中，示例：

// 假设已获取到保存后的project对象
ReportV2 report = ReportV2.builder()
    .id(UUID.randomUUID().toString())
    .name("测试报表")
    .project(project) // 设置关联的Project对象
    .build();
reportRepository.save(report);

4. 验证数据库约束 #

确认reports表的project_id字段已正确建立外键关联到projectv2表的id字段，若不存在则手动添加：

ALTER TABLE [dbo].[reports] ADD CONSTRAINT [FK_reports_projectv2] FOREIGN KEY([project_id])
REFERENCES [dbo].[projectv2] ([id])
GO

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