Cannot add or update a child row: a foreign key constraint fails JPA/Hibernate

[Nicolas Allo]

The application server is JBOSS 4 and I use JPA/HIbernate but the error is also present with JPA/TopLink.


I got a table " belong_to " who does the interaction between my cocktails and my categories of cocktails by referencing a line for a couple (cocktail/category)
A cocktail can have several categories and the inverse is also true.

The problem is present when i try to insert a line into "belong_to".

Of course, i want my architecture allows me in case i remove a cocktail, it will remove automatically each line of my table "belong_to" where my cocktail exists.
The same idea for my categories.


These are my 3 tables:

CREATE TABLE cocktail (id INT NOT NULL AUTO_INCREMENT, name TEXT, recipe TEXT, PRIMARY KEY(id));
 
CREATE TABLE cocktail_category (id INT NOT NULL AUTO_INCREMENT, name TEXT, PRIMARY KEY(id));
 
CREATE TABLE belong_to (cocktail_id INT NOT NULL, category_id INT NOT NULL, PRIMARY KEY (cocktail_id, category_id));

These are my 3 entities:

@Entity
@Table(name="cocktail")
public class Cocktail {
    
	@Id
	@Column(name = "id", nullable = false)
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;
 
	@Column(name = "name", nullable = false, unique = true)
    private String nomCocktail="";
	
	@Column(name = "recipe", nullable = false)
    private String recette="";
	
 
	 // relation Cocktail (many) -> Categorie (many) via une table de jointure belong_to
	 // belong_to(category_id)  is foreign key onCategorieCocktail(id)
	 // belong_to(cocktail_id) is foreign key on Cocktail(id)
	 // cascade=CascadeType.PERSIST : persistance d'1 cocktail entraîne celle de ses categories
	 @ManyToMany(cascade={CascadeType.PERSIST})
	 @JoinTable(name="belong_to",inverseJoinColumns = @JoinColumn(name = "category_id"))	 
private List<CategorieCocktail> categoriescocktails = new ArrayList<CategorieCocktail>();
// getters and setters   

 

   
 
 CategorieCocktail.java
@Entity
@Table(name="cocktail_category")
public class CategorieCocktail {
	@Id
	@Column(name = "ID", nullable = false)
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Integer id;
	
	@Column(name = "name", nullable = false)
    private String nomCategorieCocktail="";
   
	 // relation Categorie (many) -> Cocktail (many) via une table de jointure belong_to
	 // belong_to(category_id) est clé étangère sur CategorieCocktail(id)
	 // belong_to(cocktail_id) est clé étangère sur Cocktail(id)
	  //cascade=CascadeType.PERSIST : persistance d'1 cocktail entraîne //celle de ses categories
	 @ManyToMany(cascade={CascadeType.PERSIST})
	 @JoinTable(name="belong_to",joinColumns = @JoinColumn(name = "category_id"),
	inverseJoinColumns = @JoinColumn(name = "cocktail_id"))
	 private Set<Cocktail> cocktails = new HashSet<Cocktail>();
    
  //constructors and getters...
   
 

 
@Entity
@Table(name="belong_to")
public class CategoryCocktailId {
	
	@Embeddable
	public static class Id implements Serializable {
 
		@Column(name = "cocktail_id", nullable = false)
		private Integer cocktail_id;
		
		@Column(name = "category_id", nullable = false)
		private Integer category_id;
		
// getters et setters
	}
 
	@EmbeddedId
	public Id id = new Id();
	
	@ManyToOne()
	@JoinColumn(name="cocktail_id",insertable=false,updatable=false)
	private Cocktail cocktail;
	
	@ManyToOne()
	@JoinColumn(name="category_id",insertable=false,updatable=false)
	private CategorieCocktail categorie;
	
		
	public CategoryCocktailId()  {
		
	}
	
	public CategoryCocktailId(Cocktail cocktail, CategorieCocktail categorie) {
		getId().setCategory_id(category.getId());
		getId().setCocktail_id(cocktail.getId());
 
		this.setCategorie(categorie);	
		this.setCocktail(cocktail);
		//this.cocktail.getCategoriescocktails().add(categorie);
	}
 
//getters et setters
	        	
}
 


   

I got a mistake when i persist a CategoryCocktailId despite my entity is well formed
This is my test:

  


		EntityManager em = getEntityManager();
		cat1= new CategorieCocktail("tonique");
		EntityTransaction tx = em.getTransaction();
		tx.begin();

		em.persist(cat1);
		tx.commit();
// ok! 
		c1 = new Cocktail("cocktail01", "Versez le jus");
		
		tx.begin();
		em.persist(c1);
		tx.commit();
 
//everything is still ok 	
	
	CategoryCocktailId catcock = new CategoryCocktailId(c1,cat1);
		tx.begin();
		em.persist(catcock);
[color=red]tx.commit();[/color] Error here

  

This is the error:

Exception in thread "main" javax.persistence.RollbackException: Error while commiting the transaction
....
Caused by: org.hibernate.exception.ConstraintViolationException: Could not exe...cute JDBC batch update
	
[color=darkred]Caused by: java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`jpa`.`belong_to`, CONSTRAINT `FKD2D27D7B2A5306E7` FOREIGN KEY (`cocktail_id`) REFERENCES `cocktail` (`id`))[/color]
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1213)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:912)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
	... 10 more

    

DOes someone has an idea to deal with this problem?

It should be a really common type of solution used to deal with interactif objects like this.?

Thank you very much

[Mark Uppeteer]

Have a look at:

manytomany

it looks simpler than what you are doing.

[Nicolas Allo]

Thank you!!

[Nicolas Allo]

And what if i want to add a colum in my table "belong_to" a column whose the name is "quantity". Quantity depends of a cocktail and a category for example.
I don't find a solution..

Thank you.