Spring JPA stop creating a new table with relationship

Question

I am trying to stop my relationship making new tables. I have tried multiple approaches to this problem, but there seems to be an error every way I turn. For instance when I try the following code:

//other variables
@OneToMany(fetch = FetchType.LAZY ,cascade = CascadeType.ALL)
@JoinColumn(name = "user_id")
private List<User> users= new ArrayList<>();

I get the following error:

Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`eb322`.`#sql-3140_2e7`, CONSTRAINT `FK20sqpkpotyyf5wx4jfmp519lu` FOREIGN KEY (`user_id`) REFERENCES `year` (`year_id`))

I have checked all my tables and indexes in the database and I cannot find this constraint anywhere. How do I go about removing it. I basically want to have my schema be like this:

Year will have a list of all students, teachers. When a student is enrolled they will be added to that year etc.

If I don't add the join Column I simply get another table saying

Year.students

How do I combine these together.

This is my student class just incase there's something wrong here:

public class Student{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "user_id")
private int User_id;
}

How I am adding data to year table

//get data about student
Student s = ssrepo.findByName(name);
Year y = yyrepo.findByYear(year);
List<Student> students = y.getStudents();
students.add(s);
yyrepo.save(y)

can you share how are you trying to add data to year table – Ali Tahir Mar 08 '21 at 16:13 — Ali Tahir, Mar 08 '21 at 16:13
I'll amend the question with the answer – Dank C noob Mar 08 '21 at 16:14 — Dank C noob, Mar 08 '21 at 16:14

score 0 · Accepted Answer · answered Mar 08 '21 at 16:54

You seem to be using Unidirectional OneToMany relationship

Hibernate uses an association table to map the relationship so when you remove @JoinColumn annotation an association table is created.

As Year has one to many relationship with student, the type of the List should be List<Student> instead of List<User>

@OneToMany(fetch = FetchType.LAZY ,cascade = CascadeType.ALL)
@JoinColumn(name = "user_id")
private List<Student> users= new ArrayList<>();

And using OneToMany Unidirectional association is normally not recommended because of its performance issues. You can consider using bidirectional association. It would be something as follows

public class Year {

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

@Column(name = "TYPE_ID")
private Long typeId

@Column(name = "TYPE")
private Boolean type // 1 or 0 to know if typeId is of student or teacher

@Column(name = "YEAR")
private Date year


@OneToMany(mappedBy="typeId", fetch = FetchType.LAZY ,cascade = CascadeType.ALL)
private List<Student> students;

@OneToMany(mappedBy="typeId", fetch = FetchType.LAZY ,cascade = CascadeType.ALL)
private List<Teacher> teachers;

}

public class Teacher{

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

@ManyToOne
@JoinColumn(name="TYPE_ID", nullable=false)
 private Year typeId;

}

public class Student{

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

@ManyToOne
@JoinColumn(name="TYPE_ID", nullable=false)
 private Year typeId;

}

Thank you for this, I will try it! Will this allow me to have the same student to be added to multiple years? As it would be a year like 2019-2020 rather than year 8 for instance? — Dank C noob, Mar 08 '21 at 17:20
yes. It should. I haven't tested this code so if you face any problem. let me know — Ali Tahir, Mar 08 '21 at 17:25
`Caused by: java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`eb322`.`#sql-3140_59a`, CONSTRAINT `FKsrji3x3snoiiq1s2xei5lon2w` FOREIGN KEY (`type_id`) REFERENCES `student` (`student_id`))` I am getting this error, I have checked the student and the year constraints and I cannot find it, any idea how I can remove this constraint? — Dank C noob, Mar 08 '21 at 18:52
List and List reference puts a constraint on the same column. but a foreign key constraint must reference exactly one table. you can remove this constraint and it should work. if you really need this fk constraint you will need to change the db design and will probably need to add a new table. — Ali Tahir, Mar 09 '21 at 03:29

score 0 · Answer 2 · edited Mar 08 '21 at 17:39

There are two ways to do this. The first is bidirectional. Where you do the mapping in the two entities. here in this link.(https://dzone.com/articles/introduction-to-spring-data-jpa-part-4-bidirection) hava exemples.

public class MyClass {
    
        @OneToMany(mappedBy = "myClass", fetch = FetchType.LAZY, 
         cascade = CascadeType.ALL, orphanRemoval = true)
        @JoinColumn(name = "user_id")
        private List<User> users;
}

mappedBy is to say who is the dominate in the relationship. In this case, MyClass has the strongest relationship.

public class Student{
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_id")
    private int id;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private MyClass myClass;
}

I believe that this is the best way, because her realities are apparent in both entities. There is a way to do it in a unidirectional way. Exemple in link (How to define unidirectional OneToMany relationship in JPA)

Spring JPA stop creating a new table with relationship

2 Answers2