Error: More than one row with the given identifier was found - although tables relationship is 1-to-1

From the relationship design point of view, I have a table called Request that have list of customer request for changes. Each customer request will have a 1-to-1 relationship with RequestStatus table.

Which mean, each request will have only 1 type of status: Pending, Assigned, Completed. Thus, the 1-to-1 relationship.

In my pojo code, this is my Request file:

@Entity
@Audited
@Table(name = "requests")
@AuditTable("requests_audit")
public class Request extends Auditable<String> {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@NotBlank
private String title;

@NotBlank
private String remarks;

private String fileName;

private String fileUrl;

private String fileType;

private Long creatorRefId;

private String requestStatusRef;

@OneToOne
@JoinColumn(name = "user_id", referencedColumnName = "id", nullable = true)
private User user;

@OneToOne
@JoinColumn(name = "status_id", referencedColumnName = "id", nullable = true)
private RequestStatus status;

@OneToOne
@JoinColumn(name = "department_id", referencedColumnName = "id", nullable = true)
private Department department;

@OneToOne
@JoinColumn(name = "assigned_user_id", referencedColumnName = "id", nullable = true)
private User assignedUser;

Then, this is my RequestStatus file:

@Entity
@Audited
@Table(name = "request_status")
@AuditTable("request_status_audit")
public class RequestStatus {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Enumerated(EnumType.STRING)
@Column(length = 100)
private EnumRequestStatus status;

public RequestStatus() {
}

public RequestStatus(EnumRequestStatus status) {
    this.status = status;
}

public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public EnumRequestStatus getStatus() {
    return status;
}

public void setStatus(EnumRequestStatus status) {
    this.status = status;
}

@OneToOne(mappedBy = "status")
private Request request;

}

From the data point of view, at Request table, for sure there will be possibility of repeating RequestStatus such as:

Table for Request with sample data:

id	title	remarks	status
1	A	A	1
2	B	B	2
3	C	C	3
4	D	D	1

Question: Sounds like, from data point of view, I should have many to one relationship (from Request to RequestStatus) but, it seems not working.

For this kind of unidirectional relationship, in which RequestStatus is just a “more info” of the given status, should not have any cascading relationship – just a reference. What should I put in terms of the relationship for this kind of cases?

So, Request should have @ManyToOne to RequestStatus? Should I put a @OneToOne relationship at RequestStatus back to Request?

Answer

Which mean, each request will have only 1 type of status: Pending, Assigned, Completed. Thus, the 1-to-1 relationship.

This is not a one-to-one. If the same status, for example Pending, can be assigned to multiple requests, it means that one Request has only one status but one status is associated to many requests. Therefore this is a many-to-one from the side of the request:

@ManyToOne
@JoinColumn(name = "status_id", referencedColumnName = "id", nullable = true)
private RequestStatus status;

If this were a one-to-one, you wouldn’t have the same status_id multiple times and you could actually use the same id for Request and RequestStatus (Meaning that you wouldn’t need the additional column status_id).