JPA insert parent/child results in MySQLIntegrityConstraintViolationException

Question

This has already been asked a number of times, but I don't find any good answers so I'll ask it again.

I have parent-children unidirectional relation as follows:

@Entity
@Table(name = "PARENT")
public class Parent {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long parentId;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
    @JoinTable(name = "CHILD", joinColumns = @JoinColumn(name = "parent_id"), inverseJoinColumns = @JoinColumn(name = "ID"))
    private List<Child> children;
}

@Entity
@Table(name = "CHILD")
public class Child {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;

    @Column(name = "PARENT_ID")
    private Long parentId;

    //some other field
}

I create an instance of the parent, assign a list of children to it and try to persist it:

Parent p = new Parent();
List<Child> children = new ArrayList<Child>();
Child c = new Child();
children.add(c);
p.addChildren(children);
em.merge(p);

When the code runs, I get the following exception:

MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (testdb.child, CONSTRAINT parent_fk FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

I'm assuming this is due to the fact that the parent is not fully inserted when the child is being attempted to be inserted.

If I don't add the children to the parent, the parent gets inserted just fine. I tried switching the GeneratedValue generation strategy but that did not help.

Any ideas how to insert the parent & the children at the same time?

Edit: Even if I persist the parent first, I'm still getting the same error. I determined it's because the parent_id is not set in the child; the child is default constructed and thus the parent_id is set to 0 which does not exist thus the foreign key constraint validation.

Is there a way to get jpa to automatically set the parent_id of the children that are assigned to the parent instance?

Answer 1

Your relationship does not have to be bi-directional. There is some mis-information in the comments here.

You also said that you had added the field "parentId" into the Child entity because you assumed that JPA needs to "know" about the parent field so that it can set the value. The problem is not that JPA does not know about the field, based on the annotations that you have provided. The problem is that you have provided "too much" information about the field, but that information is not internally consistent.

Change your field and annotation in Parent to:

@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn(name = "parent_id")
private List<Child> children;

Then remove the "parentId" from the Child entity entirely. You had previously specified a JoinTable annotation. However, what you want is not a JoinTable. A JoinTable would create an additional third table in order to relate the two entities to each other. What you want is only a JoinColumn. Once you add the JoinColumn annotation onto a field that is also annotated with OneToMany, your JPA implementation will know that you are adding a FK into the CHILD table. The problem is that JPA has a CHILD table already defined with a column parent_id.

Think of it that you are giving it two conflicting definitions of both the function of the CHILD table and the parent_id column. In one case, you have told you JPA that it is an entity and the parent_id is simply a value in that entity. In the other, you have told JPA that your CHILD table is not an entity, but is used to create a foreign key relationship between your CHILD and PARENT table. The problem is that your CHILD table already exists. Then when you are persisting your entity, you have told it that the parent_id is explicitly null (not set) but then you have also told it that your parent_id should be updated to set a foreign key reference to the parent table.

I modified your code with the changes I described above, and I also called "persist" instead of "merge".

This resulted in 3 SQL queries

insert into PARENT (ID) values (default)
insert into CHILD (ID) values (default)
update CHILD set parent_id=? where ID=?

This reflects what you want perfectly. The PARENT entry is created. The CHILD entry is created, and then the CHILD record is updated to correctly set the foreign key.

If you instead add the annotation

@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn(name = "parent_id", nullable = false)
private List<Child> children;

Then it will run the following query when it inserts the child

insert into CHILD (ID, parent_id) values (default, ?)

thus setting your FK propertly from the very beginning.