Why I'm getting an error when I'm trying to insert a new object in db?

Question

My scenario is as follows

A User can have a list of Track, corresponding to it, the Track entity contains a user id.(@OneToMany)

Whenever a new track is created, the list of tracks will be updated.

Aforementioned entities are as follows:

Track Entity

@Entity
@Table(name ="track")
public class Track {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long trackId;

@ManyToOne
@JoinColumn(name = "userId", nullable = false)
private User user;

@OneToOne(mappedBy = "track")
private Share share;

private String trackName;

@OneToMany(mappedBy = "pointId")
private List<Point> point;

@OneToOne(mappedBy = "track")
private TrackStatistic trackStatistic;

User Entity

@Entity
@Table(name = "user")
public class User {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "USER_ID")
private Long id;

private String firstName;

private String lastName;

@Column(unique = true)
private String username;

private String password;

@Column(unique = true)
private String email;

@Column(unique = true)
private String phoneNumber;

private int age;

private Role role;

@OneToMany(mappedBy = "shareId")
private List<Share> shares;

@OneToMany(mappedBy = "trackId")
private List<Track> tracks;

}

createTrack method is as follows

public Track createTrack(String username, TrackDTO trackDTO) {
    //Find user
    User user = userRepository.findByUsername(username);

    //Convert Dto to Entity
    Track track = modelMapper.map(trackDTO, Track.class);

    //Update user track list
    user.getTracks().add(track);

    //Update track
    track.setUser(user);

    //save user
    userRepository.save(user);

    //save track
    return trackRepository.save(track);
}

Notice that TrackDTO is a corresponding Dto class of Track entity

---

When I ran createTrack, I faced the following error:

2020-01-18 20:48:23.315 ERROR 14392 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper:
 Cannot add or update a child row: a foreign key constraint fails (`thdb`.`track`, CONSTRAINT `FK5cftk3hw8vfnaigtj063skvxs` FOREIGN KEY (`track_id`) REFERENCES `user` (`user_id`))

2020-01-18 20:48:23.338 ERROR 14392 --- [nio-8080-exec-1] 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

java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails 
(`thdb`.`track`, CONSTRAINT `FK5cftk3hw8vfnaigtj063skvxs` FOREIGN KEY (`track_id`) REFERENCES `user` (`user_id`))

Answer 1

Refactored the relation between entities and the issue still persist. As a small preview for the relation between track and user looks like below: Track entity

@Entity
@Table(name ="TRACK")
public class Track {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "TRACK_ID")
    private Long trackId;

    private String trackName;

    @OneToMany(cascade = CascadeType.ALL,
            orphanRemoval = true)
    @JoinColumn(name = "POINT_ID")
    private List<Point> point;

    @OneToOne
    @JoinColumn(name="TRACK_STATISTIC_ID")
    private TrackStatistic trackStatistic;

    private long numberOfLikes;

    private Date creationTime;

User entity

@Entity
@Table(name = "USER")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "USER_ID")
    private Long userId;

    private String firstName;

    private String lastName;

    @Column(unique = true)
    private String username;

    private String password;

    @Column(unique = true)
    private String email;

    @Column(unique = true)
    private String phoneNumber;

    private int age;

    private Role role;

    private boolean locked;

    private long numberOfReports;

    @JsonIgnore
    @ManyToMany()
    @JoinTable(name="FOLLOWER",
            joinColumns={@JoinColumn(name="USER_ID")},
            inverseJoinColumns={@JoinColumn(name="FOLLOWER_ID")})
    private Set<User> followed = new HashSet<User>();

    @JsonIgnore
    @ManyToMany(mappedBy="followed")
    private Set<User> follower = new HashSet<User>();

   //How it was before
    @OneToMany
    @JoinColumn(name = "TRACK_ID")
    private List<Track> tracks;

   //How is it now
    @OneToMany
    @JoinColumn(name = "USER_ID")
    private List<Track> tracks;


    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "SHARE_ID")
    private List<Share> shares;

and the method that is called when a track is created is:

public Track createTrack(String username, TrackDTO trackDTO) {
        Track track = modelMapper.map(trackDTO, Track.class);
        Track newTrack = trackRepository.save(track);
        return newTrack;
    }

So the issue was on the @JoinColumn annotation on tracks list, I put the name TRACK_ID instead of USER_ID

Answer 2

Edited

---

By active cascading, when you save User, there is no need to saving Track again. so I made some changes in your code as follows, hope it helps

1- Add cascade = CascadeType.ALL to User entity

2- Add targetEntity = Track .class, mappedBy = "user" to User entity

3- Add @ManyToOne(targetEntity = User.class) @JoinColumn(name = "USER_FK") to Track entity

4-Delete trackRepository.save(track);

5- Just save user(userRepository.save(user)). by cascade, it saves the track too.

6- Return the last track in user list.(Newest saved track)

---

I coded aforementioned edits as follows

User Entity

@Entity
@Table(name = "user")
public class User {

  //other properties

   @OneToMany(targetEntity = Track.class , mappedBy = "user" ,cascade = CascadeType.ALL)
   private List<Track> tracks;

   //getters and setters   
  
  }

Track Entity

@Entity
@Table(name ="track")
public class Track {
 
//other properties

@ManyToOne(targetEntity = User.class)
@JoinColumn(name = "USER_FK",nullable = false)
private User user;

//getters and setters

}

createTrack method

public Track createTrack(String username, TrackDTO trackDTO) {

    //Find user
    User user = userRepository.findByUsername(username);

    //Convert Dto to Entity
    Track track = modelMapper.map(trackDTO, Track.class);

    //Update User and Track
    user.getTracks().add(track);
    track.setUser(user);

    //save user
    User result = userRepository.save(user);
    
    //find saved track (you can fetch track by other ways too)
    Track savedTrack = result.getTracks().get(result.getTracks().size()-1);

    return savedTrack;
}

Answer 3

You should save the track before the user.

public Track createTrack(String username, TrackDTO trackDTO) {
    User user = userRepository.findByUsername(username);
    Track track = modelMapper.map(trackDTO, Track.class);
    user.getTracks().add(track);
    track.setUser(user);
    userRepository.save(user); // 1
    return trackRepository.save(track); // 2
}

If it were plain old SQL queries, you code would go wrong:

• The (1) query would do insert into USER_TRACK (<USER>, <TRACK>) but while the user is saved, <TRACK> is not yet saved, so you can't assign in to the user/track relation because you lack the ID.
• The (2) query would do insert into TRACK (<TRACK>) and generate an id but it happens after.

You are probably missing some JPA mapping, because I think that JPA would normally take care of that for you.

Answer 4

The relationship is cyclic, user contains track(s) and a track contains user(s). There is fk constraint in both directions, so the issue. You can fix it by keeping user independent with no reference to track and keep the track refer the user. There are other ways to fix the relationship, the main point to take out is to avoid cyclic relationship