CODEWITHSUNDEEP
javapostgresqlspring-boot
Jeff Gaer
Jeff Gaer

Reputation: 401

Spring boot delete is not cascading it is setting the foriegn key to null and then failing on a null constraint

Using deleteById in spring boot with a one to many relationship the query being generated tries to set the foreign key to null in the referenced entities instead of deleting them. I am using the default repository deleteById

I've set the Cascadetype to ALL and OrpahnRemoval to true on the definition of the foreign key in the entity and I've set ON DELETE CASCADE in the DDL that created the table.

Here is the delete operation in the controller class

@Transactional
@DeleteMapping("transferImage/{imageId}")
public void  deleteTransferImage(@PathVariable int imageId) {
    repository.deleteById(imageId);
}

Here is the reference from the parent to the child entity

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, OrphanRemoval = true)
@JoinColumn(name = "TRANSFER_IMAGE_ID")
private List<TransferPartition> partitions = new ArrayList<>();

Here is the definition of the foreign key in the child entity

@JsonIgnore
@ManyToOne
@JoinColumn(name = "TRANSFER_IMAGE_ID", referencedColumnName = "TRANSFER_IMAGE_ID")
private TransferImage image;

Here is the DDL used to create the two tables

CREATE TABLE TRANSFER_IMAGE (
TRANSFER_IMAGE_ID       SERIAL CONSTRAINT TRANSFER_IMAGE_PK PRIMARY KEY,
IMAGE_NAME              VARCHAR(50) CONSTRAINT TRANSFER_IMAGE_NAME_UK UNIQUE NOT NULL ,
REQUESTED_PART_SIZE_MB  INTEGER NOT NULL,
SIZE_BYTES              INTEGER NOT NULL,
IMAGE_MD5_HASH          VARCHAR(100),
NUMBER_PARTITIONS       INTEGER,
DELETED                 BOOLEAN  NOT NULL
);

CREATE TABLE TRANSFER_PARTITION (
TRANSFER_PARTITION_ID   SERIAL CONSTRAINT TRANSFER_PARTITION_PK PRIMARY KEY,
TRANSFER_IMAGE_ID       INTEGER NOT NULL CONSTRAINT TRANSFER_PARTITION_IMAGE_FK REFERENCES TRANSFER_IMAGE ON DELETE CASCADE ON UPDATE CASCADE,
PARTITION_NUMBER        INTEGER NOT NULL,
PARTITION_MD5_HASH      VARCHAR(100) NOT NULL,
SIZE_BYTES              INTEGER NOT NULL
);

Here is the query that appears in the log

 Hibernate:
  select
    transferim0_.transfer_image_id as transfer1_13_0_,
    transferim0_.deleted as deleted2_13_0_,
    transferim0_.image_md5_hash as image_md3_13_0_,
    transferim0_.image_name as image_na4_13_0_,
    transferim0_.number_partitions as number_p5_13_0_,
    transferim0_.requested_part_size_mb as requeste6_13_0_,
    transferim0_.size_bytes as size_byt7_13_0_,
    partitions1_.transfer_image_id as transfer5_14_1_,
    partitions1_.transfer_partition_id as transfer1_14_1_,
    partitions1_.transfer_partition_id as transfer1_14_2_,
    partitions1_.transfer_image_id as transfer5_14_2_,
    partitions1_.partition_number as partitio2_14_2_,
    partitions1_.partition_md5_hash as partitio3_14_2_,
    partitions1_.size_bytes as size_byt4_14_2_
from
    transfer_image transferim0_
left outer join
   transfer_partition partitions1_
       on transferim0_.transfer_image_id=partitions1_.transfer_image_id
where
      transferim0_.transfer_image_id=?
Hibernate:
update
    transfer_partition
 set
     transfer_image_id=null
 where
     transfer_image_id=?

I was expecting that all the child entities(TransferPartition) that reference the parent (TransferImage) to be deleted when I delete the parent by its primary key. Instead I get a null constraint error referring to the foreign key column. It looks to me like the generated SQL is setting the foreign key column to null instead of deleting the row.

ERROR: null value in column "transfer_image_id" violates not-null constraint
Detail: Failing row contains (1, null, 1, asdfaa1-1, 20000000).

If I delete the image from the transfer_image table from the psql prompt the delete cascades properly and the referenced partitions are removed. 

delete  from transfer_image i where i.transfer_image_id = 1

Upvotes: 1

Views: 2344

Answers (1)

FredvN
FredvN

Reputation: 544

Your problem is in the definition of the foreign key relation in the TransferImage class.

Instead of 

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, OrphanRemoval = true)
@JoinColumn(name = "TRANSFER_IMAGE_ID")
private List<TransferPartition> partitions = new ArrayList<>();

you should use

@OneToMany(mappedBy="image", cascade = CascadeType.ALL, fetch = FetchType.EAGER, OrphanRemoval = true)
private List<TransferPartition> partitions = new ArrayList<>();

See also https://www.baeldung.com/hibernate-one-to-many

Upvotes: 4

Related Questions