[FIXED] Duplicate or Null error when trying to send POST request for JSON Object with Foreign Key using Spring JPA

Issue

Here's my parent entity:

@Entity(name = "DrivingInstructor")
@Table(name = "driving_instructor")
@Getter
@Setter
@NoArgsConstructor
public class DrivingInstructor {
    @Id
    @Column(name = "driving_instructor_id")
    private long drivingInstructorId;

    @Column(name = "driving_instructor_name")
    @Size(max = 128)
    private String drivingInstructorName;

    @Column(name = "specialization")
    @Size(max = 200)
    private String specialisation;
}

And here's my supposed child entity:

@Entity(name = "DrivingStudent")
@Table(name = "driving_student")
@Getter
@Setter
@NoArgsConstructor
public class DrivingStudent {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "driving_student_id")
    private long drivingStudentId;
    
    @Column(name = "driving_student_name")
    @Size(max = 128)
    private String drivingStudentName;

    @ManyToOne(cascade = CascadeType.ALL, targetEntity = DrivingInstructor.class)
    @JoinColumn(name = "driving_instructor_id", referencedColumnName = "driving_instructor_name", insertable = false, updatable = false)
    private DrivingInstructor drivingInstructor;
    
}

Here's the relevant chunk of my service class for inserting/saving an instance of a DrivingStudent into the database:

@RequestMapping(path = "api/v0/driving-school")
@RestController
@AllArgsConstructor
public class DrivingStudentRestController {
    private final DrivingStudentServiceImpl drivingStudentServiceImpl;

    @PostMapping
    Long insertOrUpdateDrivingStudent(@Valid @RequestBody DrivingStudent drivingStudent) {
        return drivingStudentServiceImpl.insertOrUpdateDrivingStudent(drivingStudent);
    }
}

DrivingStudentServiceImpl is just an abstraction layer for Repository class that extends JpaRepository<DrivingStudent, Long>, so insertOrUpdateDrivingStudent() is practically just using the save() method from CrudRepository.

An instance of DrivingInstructor is already pre-inserted with drivingInstructorId of 1, and so I tried to execute a POST request via Postman using this JSON object:

{
    "drivingStudentName": "Peter Parker",
    "drivingInstructor": {"drivingInstructorId": 1}
}

And I'm getting this exception:

2021-08-27 20:03:37.554 ERROR 16108 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
        ERROR: duplicate key value violates unique constraint "driving_instructor_pkey"
            Detail: Key (driving_instructor_id)=(1) already exists.

2021-08-27 20:03:37.590 ERROR 16108 --- [nio-8080-exec-3] 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 [driving_instructor_pkey]; 
                        nested exception is org.hibernate.exception.ConstraintViolationException: 
                            could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "driving_instructor_pkey"
  Detail: Key (driving_instructor_id)=(1) already exists.

I also tried revising my RestController's PostMapping to look like this, but still nothing changes:

@RequestMapping(path = "api/v0/driving-school")
@RestController
@AllArgsConstructor
public class DrivingStudentRestController {
    private final DrivingInstructorRepository drivingInstructorRepository;
    private final DrivingStudentServiceImpl drivingStudentServiceImpl;
    
    @PostMapping
    Long insertOrUpdateDrivingStudent(@Valid @RequestBody DrivingStudent drivingStudent) {
        Optional<DrivingInstructor> drivingInstructor = drivingInstructorRepository.findById(drivingStudent.getDrivingInstructor().getDrivingInstructorId());
        if (drivingInstructor.isPresent()) {
            drivingStudent.setDrivingInstructor(drivingInstructor.get());
            return drivingStudentServiceImpl.insertOrDrivingStudent(drivingStudent);
        }
        return null;
    }
}

The error I am getting then changed to:

2021-08-27 21:36:58.622 ERROR 11388 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
    ERROR: null value in column "driving_instructor_number" of relation "driving_student" violates not-null constraint
        Detail: Failing row contains (Peter Parker, null).
2021-08-27 21:36:58.632 ERROR 11388 --- [nio-8080-exec-4] 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 [driving_instructor_number" of relation "driving_student]; 
                        nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: null value in column "driving_instructor_number" of relation "driving_student" violates not-null constraint
  Detail: Failing row contains (Peter Parker, null).

There are stuff I've tried but most exceptions simply end up with either of those two. All I really wanted to do was insert an instance of DrivingStudent into the database using POST request, with a foreign key connecting it to a DrivingInstructor instance, and then of course, be able to retrieve those data.

I am able to do insert data manually into the database using the statement: INSERT INTO driving_student VALUES ('Peter Parker', 1);

And I am able to retrieve that data in JSON format using GET method. So far, my only problem really is how to deal with the POST method.

Solution

Ok, I just changed/simplified the annotations in DrivingStudent's drivingInstructor JoinColumn field from this:

@ManyToOne(cascade = CascadeType.ALL, targetEntity = DrivingInstructor.class)
@JoinColumn(name = "driving_instructor_id", referencedColumnName = "driving_instructor_name", insertable = false, updatable = false)
private DrivingInstructor drivingInstructor;

to this:

@ManyToOne
@JoinColumn(name = "driving_instructor_id")
private DrivingInstructor drivingInstructor;

and it somehow worked... I have no idea why though.

Answered By - dglz