org.hibernate.exception.ConstraintViolationException when using multiple ManyToMany relations

Question

When running the following code, I get a org.hibernate.exception.ConstraintViolationException exception

complaining that

org.h2.jdbc.JdbcSQLException: NULL not allowed for column "SECONDARYELEMENTS_ID";

I know that this is caused by having two @ManyToMany relations from the Container object to the Element objects. If I remove

@ManyToMany(cascade = CascadeType.ALL)
List<Element> secondaryElements;

from the the Container class everything runs fine.

What am I missing here?

Let me know if you need more information.

@Transactional
public class JPA2Runner {
     //hidding Spring Data JPA repository
     @Autowired 
     ContainerDAO containerDAO;
     public boolean run() throws Exception{
         Container container1 = new Container( );
         Container container2 = new Container( );
         Element element1 = new Element( container1, container2);
         Element element2 = new Element( container2, container1);
         container1.getPrimaryElements().add(element1);
         container1.getSecondaryElements().add(element2);
         container2.getPrimaryElements().add(element2);
         container2.getSecondaryElements().add(element1);
         containerDAO.saveContainer(container1);
         return true;
     }
}

@Entity
public class Container extends AbstractEntity {          
    @ManyToMany(cascade = CascadeType.ALL)
    List<Element> primaryElements;
    @ManyToMany(cascade = CascadeType.ALL)
    List<Element> secondaryElements;

    public Container( ){
        primaryElements =new ArrayList<Element>();
        secondaryElements = new ArrayList<Element>();
    }
}

@Entity
public class Element extends AbstractEntity {
    @ManyToOne(cascade = CascadeType.ALL)
    private Container dedicatedContainer1;
    @ManyToOne(cascade = CascadeType.ALL)
    private Container dedicatedContainer2;

    public Element(){}      
    public Element(Container container1, Container container2){
        this.dedicatedContainer1 = container1;
        this.dedicatedContainer2 = container2;
    }
}

Update 1: Could it be that it is required to specify the @JoinTable in case there are multiple relations to the same type?

Update 2: Thanks to @ducksteps hints and comments I was able to find a workaround for the issue. The problem is that the above definition generates a join table with keys for both element lists, i.e.

create table Container_Element (Container_id bigint not null, secondaryElements_id bigint not null, primaryElements_id bigint not null)

however, saving a container generates the following insert in the join table

 insert into Container_Element (Container_id, primaryElements_id) values (?, ?)

which causes the ConstraintViolation exception. A fix seems to be to explicitly define two Join tables using

@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name="Container_PrimaryElements")
List<Element> primaryElements;
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name="Container_SecondaryElements")
List<Element> secondaryElements;

which seems to work.

However, I am still wondering whether

• there are better solutions for this issue?
• using a join table for both ManyToMany relations "should" actually work (according to the specification)?

Answer 1

I can think of two possible reasons:

1. The NOT NULL or REFERENCES constraint for the SECONDARYELEMENTS_ID column in the CONTAINER_ELEMENT relation table is not deferrable. During the saveContainer() call, you persist a relation to a non-persisted entity. As this relation is circular (Element relates to Container relates to Element relates to [...]), this cannot be solved by reordering. I'm not sure how h2 handles this, but I've run into this problem when working with Postgres.

2. ID generation (in your case) doesn't work with cascade rules. For some reason, Element doesn't get a generated ID - so JPA persists it with ID NULL (if your ELEMENT table allows that).

Let me know if you need more information.

From you:

Debug output, especially the generated SQL statements (ideally with responses) would help finding out at which point your transaction fails. Your table definitions (CREATE TABLE [...], especially the constraint definitions) would be useful to find out if the first reason might be a problem in this case.

From others:

Someone more experienced with h2 could tell if you need some "magic" (like making REFERENCES deferrable in Postgres) for inserting data with circular relations.

Update 1: Could it be that it is required to specify the @JoinTable in case there are multiple relations to the same type?

Possible. The spec has this example:

Entity Employee is mapped to a table named EMPLOYEE. Entity Patent is mapped to a table named PATENT. There is a join table that is named EMPLOYEE_PATENT (owner name first). This join table has two foreign key columns. One foreign key column refers to table EMPLOYEE and has the same type as the primary key of EMPLOYEE. This foreign key column is named EMPLOYEE_, where denotes the name of the primary key column of table EMPLOYEE.The other foreign key column refers to table PATENT and has the same type as the primary key of PATENT. This foreign key column is named PATENTS_, where denotes the name of the primary key column of table PATENT.

So the table name is derived from the entity names, and it's column names are derived from the relation target column names and the field names. What structure does your join table have? If it has more than two columns, then that's your problem.

Update 2: [...] better solutions for this issue?

Depends on your use case. You could use another level of inheritance, i.e. PrimaryElement extends Element and SecondaryElement extends Element and then use a single field List<Element> elements to store your data (you can still query for a specific type). Of course this only works if the type of the Element is primary xor secondary.

Otoh, using two join tables might even work out better, again depending on your use case (additional JOIN vs. bigger table).

using a join table for both ManyToMany relations "should" actually work (according to the specification)

Try removing the NOT NULL constraints for primaryElements_id and secondaryElements_id.