java外键改成null,HIbernate无法使用外键删除实体。外键设置为null

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 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?

解决方案

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 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.