[FIXED] Cannot delete entity with CrudRepository with foreign key constraint

Issue

I cannot delete a parent entity (category) which has many child entities (product)

I do not want a CASCADE delete. I want to set the foriegn key value to NULL in the child entity (product). A product can exist without a category.

As far as I can tell, the only way to do this in Spring/JPA is to iterate through all the children and set the entity to null, to nullify the relationship. (It seems tedious... i know! ... surely there should be an annotation for this like there is for CASCADE)

My method looks like this :

public void deleteCategory(CategoryEntity categoryEntity) {
    CategoryEntity category= categoryRep.findByName(categoryEntity.getName());

    List<ProductEntity> ps = productRep.findByCategory(category);
    for (ProductEntity p : ps) {
        p.setCategory(null);
        productRep.save(p);
    }

    categoryRep.delete(category);
}

(Both ProductRep and CategoryRep are implementations of CrudRepository)

This should work, but I get the error

Referential integrity constraint violation: "FKQX9WIKKTSEV17CTU0KCPKRAFC: PUBLIC.PRODUCT FOREIGN KEY(CATEGORY) REFERENCES PUBLIC.CATEGORY(CAT_ID)

So basically this does not seem to work.

Am I missing a commit or flush somewhere? Am I missing a transaction annotation somewhere?

As far as I know the CRUDRepository "save" and "update" are transactional, so this code should work.

Thankyou!

*** UPDATE *** Here are my entities**

@Data
@Entity
@Table(name = "category")
public class CategoryEntity {

    @Id
    @Column(name = "cat_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;


    @Column(name = "name")
    private String name;
....
}

@Data
@Entity
@Table(name = "product")
public class ProductEntity {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    //TODO change cascade to nullable. products can also exist without a category
    @ManyToOne
    @JoinColumn(name = "category", nullable = true, updatable = false)
    private CategoryEntity category = new CategoryEntity();
.....

}

Solution

I recreated the issue and found that the constraint violation happening because you have made category as updateable = false. This needs to be removed.

    @ManyToOne
    @JoinColumn(name = "category")
    private CategoryEntity category = new CategoryEntity();

Answered By - johnnyutts
Answer Checked By - Terry (JavaFixing Volunteer)