HIbernate Can't delete Entity with foreign key. Foreign key gets set to null

Question

This question has been asked in many forms here but none of the solutions seem to work for me. I'm trying to delete the parent entity and I want all of the child entities to also be deleted.

My entities:

@Entity
@Table(name = "item", catalog = "myshchema")
public class Item implements java.io.Serializable {

@JoinColumn(name = "item_id", insertable = false, updatable = false, nullable = false)
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
private Set<ItemCategory> categories;

/* Getters and Setters and other fields*/
}

Table for Item:

CREATE TABLE `item` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`store_id` int(11) NOT NULL,
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_id_UNIQUE` (`item_id`),
KEY `FK_ITEM_STORE_ID_idx` (`store_id`),
CONSTRAINT `FK_ITEM_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store`   (`store_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8;

And my other entity

@Entity
@Table(name = "item_category", catalog = "myschema")
@IdClass(ItemCategoryIndex.class)
public class ItemCategory implements java.io.Serializable {

@Id
@Column(name = "category_id", unique = true, nullable = false, insertable = false, updatable = false)
private Integer categoryId;
@Id
private Store store;
@Id
private Item item;
@Id
private String categoryName;

/* Getters and Setters */
}

Table for ItemCategory:

CREATE TABLE `item_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`store_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`category_name` varchar(45) NOT NULL,
PRIMARY KEY (`category_id`),
UNIQUE KEY `category_id_UNIQUE` (`category_id`),
UNIQUE KEY `IDX_UNIQUE_STORE_CATEGORY`     (`store_id`,`item_id`,`category_name`) USING BTREE,
KEY `FK_CATEGORY_STORE_ID_idx` (`store_id`),
KEY `FK_ITEM_CATEGORY_ID_idx` (`item_id`),
CONSTRAINT `FK_CATEGORY_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES     `store` (`store_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_ITEM_CATEGORY_ID` FOREIGN KEY (`item_id`) REFERENCES `item`     (`item_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;

I try to delete the item like this:

Item item = entityManager.find(Item.class, idList.get(i));
entityManager.remove(item);

My logs show that Hibernate is trying to set the primary key for ItemCategory to null:

Hibernate: update myschema.item_category set item_id=null where item_id=?
ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions 146 - Column 'item_id' cannot be null

I even tried looping through the child records and deleting them manually, but Hibernate still issues this update to null query. What am I doing wrong?

Answer 1

I have to break your problem down to two parts

First - let's talk about your database schema design.

According to your schema, item and item_category has a one-to-many relationship meaning an item can have/be-assigned-to different categories but different items cannot have/be-assigned-to the same category.

That is totally fine if it is indeed your business requirement, I mention it because it does not make sense to me and this circumstance rarely happens.

If what you want is that a category can have multiple items and vice versa, itemand item_category must be a many-to-many relationship. There should be a join table additionally.

Second - let's say the schema don't change

ItemCategory is the owner of the relationship because it has a foreign key item_id refering to item table. So the ItemCategoy should look roughly like this:

@Entity
@Table(name = "item_category")
public class ItemCategory {

  @Id
  private Integer categoryId;

  private Store store;

  @ManyToOne
  @JoinColumn(name="item_id", /*cascade = ...*/)
  private Item item;

  private String categoryName;

  /* Getters and Setters */
}

Your Item entity will be roughly like this:

@Entity
@Table(name = "item", catalog = "myshchema")
public class Item implements java.io.Serializable {

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true, mappedBy="item")
  private Set<ItemCategory> categories; //`mappedBy`used here because this entity is not the owner of the relationship according to what mentioned above

  /* Getters and Setters and other fields*/
}  

To remove all the child entities(ItemCategory) from Item , simply

em.remove(item);

The orphanRemoval is true, deleting the parent, the children will be deleted as well.