'Cannot add or update a child row: a foreign key constraint fails' when trying to add a primary composite key in Hibernate

Question

I'm working on the project of a school gradesbook implementation where there are following entities: students, subjects and grades. Each grade has one subject, one student, date and value. I'm trying to implement this relation and I decided for a grade to have a composite primary key that is composed of subject id, student id and date, here are my entity classes

Student:

@Entity
@Table(name="Students")
public class Student {
     @Id @Column(name="Student_Id")
     private int studentId;
     @Column(name="First_Name")
     private String firstName;
     @Column(name="Last_Name")
     private String lastName;
     //getters and setters
}

Subject:

@Entity
@Table(name="Subjects")
public class Subject {
    @Id @Column(name="Subject_Id")
    private int subjectId;
    @Column(name="Subject_Name")
    private String subjectName;
    //getters and setters
}

Grade (Here I created the composite primary key by adding @Id tag on Student_Id,Subject_Id and Date, where Student_Id and Subject_Id are also foreign keys):

@Entity
@Table(name="Grades")
public class Grade implements Serializable{
    @Id
    @ManyToOne @JoinColumn(name="Student_Id", foreignKey=@ForeignKey(name="FK_Grades_Student"))
    @Cascade(CascadeType.SAVE_UPDATE)
    private Student student;
    @Id
    @ManyToOne @JoinColumn(name="Subject_Id", foreignKey=@ForeignKey(name="FK_Grades_Subject"))
    @Cascade(CascadeType.SAVE_UPDATE)
    private Subject subject;
    @Column(name="Date")
    @Id
    private Date date;
    @Column(name="Letter_Value") //A,B,C,D,F
    private String letterValue;
    //getters and setters
}

And here is how I run it from Main:

public class Main {

    public static void main(String[] args) {
        Subject subject = new Subject(); subject.setSubjectId(1); subject.setSubjectName("Biology");
        Student student = new Student(); student.setStudentId(1); student.setFirstName("Jack"); student.setLastName("Smith");
        Grade grade = new Grade(); grade.setStudent(student); grade.setSubject(subject); grade.setDate(new Date()); grade.setLetterValue("B"); 

        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        Session session = sessionFactory.openSession();
        session.beginTransaction();
        session.save(grade);
        session.getTransaction().commit();
        session.close();
        sessionFactory.close();
    }
}

After running I get the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 

Cannot add or update a child row: a foreign key constraint fails (`grades_book_db`.`grades`, CONSTRAINT `FK_Grades_Subject` FOREIGN KEY (`Subject_Id`) REFERENCES `subjects` (`Subject_Id`))
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 18 more

How to fix this exception, and how can I implemented the composite key I mentioned above? I am using MySQL DBMS.

score 2 · Accepted Answer · answered Feb 15 '17 at 16:03

2

Try persisting Student and Subject objects first. Hibernate cannot find those objects on database if you do not persist them.

answered Feb 15 '17 at 16:03

bharathp

355
1
5
16

Thanks, your advice fixed the issue, I wonder though when I create a separate primary key field for `Grade` instead of the composite key, and then save a `Grade` object, hibernate saves it without any issues even though `Student` and `Subject` are not yet in the data base, why then does it stop working when I add the composite primary key to it? – Mykhailo Seniutovych Feb 16 '17 at 18:46
That's strange! From my understanding even if you have a primary key on Grade, Hibernate cannot persist Grade when Student and Subject are not available. So when you say Grade is saved on the database, what are the values in student_id and subject_id columns ? They have foreign key constraints right ? I would expect without subject and student object, there would be constraint error. – bharathp Feb 17 '17 at 08:51
I created a `Grade` object that refers `Student` and `Subject` objects, all of them have primary key=1. When I run this simple code `session.save(grade);` everything is saved nicely, foreign keys of `Grade` have the correct values of 1. I think the reason it works, in this case, is because I have `@Cascade(CascadeType.SAVE_UPDATE)` in my `Grade` class, as you can see above, so hibernate automatically facilitates that (from what I see in console it saves subject and student first and then grade), but when I add a composite primary key, hibernate can no longer deal with that, I don't know why – Mykhailo Seniutovych Feb 17 '17 at 11:06
I would assume, Hibernate would have issues persisting an object if the primary key itself is not available. When you changed your code, the primary key is no longer dependent on other objects, which allows the code to work. In future if you work with composite keys, I would recommend having a look at this post http://stackoverflow.com/questions/3585034/how-to-map-a-composite-key-with-hibernate. Sorry, I could not provide more details on your issue! – bharathp Feb 17 '17 at 12:04

'Cannot add or update a child row: a foreign key constraint fails' when trying to add a primary composite key in Hibernate

1 Answers1