外键约束应用于同一个表Spring Boot JPA

Question

在保存项目对象后保存报告时出现错误。看起来项目表正在为外键引用自己的id属性但是它应该在Reports表中引用proejct_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'.

我有一个Project类。项目包含报表列表(OneToMany)和Access对象列表，供多个用户和用户组访问。

@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;
}

Following is the reports table
the reports table contains a back reference to Project table, referencing project_id to id in project table.
It also contains list of Access object for multiple users and group of users to access 
@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;
}

**The access to any project and report can be separately provided**
@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;
}

**When I try to add a report the error received is** 
**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'.**

**Following is The create statement from Azure Sql is** 

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

----------
Following is the save report request 
[![add report request][1]][1]

[1]: https://i.stack.imgur.com/r7nDG.png

Answer 1

try this

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