Hibernate Batch Cascade Delete a Collection

Question

Unfortunately, Oracle (at least the version I am using) does not support automatic cascade deletes. Child records must be deleted separately before deleting parent records to avoid constraint violations?

When deleting a parent object with a CascadeType.DELETE set on a @OneToMany, when does Hibernate decide between deleting each child instance one by one and deleting by foreign key in batch.

For example,

PARENT table:

PARENT_ID

1

2

CHILD table:

CHILD_ID PARENT_ID

1               1

2               1

3               2

Deleting the parent could cascade the delete of the children in two ways:

delete from CHILD where child_id = 1
delete from CHILD where child_id = 2
delete from PARENT where parent_id = 1

or

delete from CHILD where parent_id = 1
delete from PARENT where parent_id = 1

I've seen Hibernate do both kinds. What I don't understand is how Hibernate decides which strategy to use. It looks like if the collection is actually initialized, it is likely do the individual deletes in the former example. Yet, this is prone to giving ConstraintViolationException errors if the collection mismatches what is in the session.

Rob van Wijk Rob van Wijk · Accepted Answer · 2010-12-28T15:45:37

I can't answer the Hibernate part of your question. But you also mentioned:

"Oracle (at least the version I am using) does not support automatic cascade deletes"

and that's the part I can contradict with an example.

First your setup of the tables with primary key constraints:

SQL> create table parent (id)
  2  as
  3  select 1 from dual union all
  4  select 2 from dual
  5  /

Table created.

SQL> alter table parent
  2    add constraint parent_pk
  3    primary key (id)
  4  /

Table altered.

SQL> create table child (id, parent_id)
  2  as
  3  select 1, 1 from dual union all
  4  select 2, 1 from dual union all
  5  select 3, 2 from dual
  6  /

Table created.

SQL> alter table child
  2    add constraint child_pk
  3    primary key (id)
  4  /

Table altered.

You probably have a regular foreign key constraint in place:

SQL> alter table child
  2    add constraint child_parent_fk
  3    foreign key (parent_id)
  4    references parent(id)
  5  /

Table altered.

And you witness that cascading deletes don't work:

SQL> delete parent
  2   where id = 1
  3  /
delete parent
*
ERROR at line 1:
ORA-02292: integrity constraint ([schema].CHILD_PARENT_FK) violated - child record found

But, if you define your foreign key constraint with the ON DELETE CASCADE clause ...

SQL> alter table child
  2    drop constraint child_parent_fk
  3  /

Table altered.

SQL> alter table child
  2    add constraint child_parent_fk
  3    foreign key (parent_id)
  4    references parent(id)
  5    on delete cascade
  6  /

Table altered.

... then child records are automatically deleted when the parent is deleted:

SQL> select * from parent
  2  /

        ID
----------
         1
         2

2 rows selected.

SQL> select * from child
  2  /

        ID  PARENT_ID
---------- ----------
         1          1
         2          1
         3          2

3 rows selected.

SQL> delete parent
  2   where id = 1
  3  /

1 row deleted.

SQL> select * from parent
  2  /

        ID
----------
         2

1 row selected.

SQL> select * from child
  2  /

        ID  PARENT_ID
---------- ----------
         3          2

1 row selected.

Regards, Rob.

Hibernate Batch Cascade Delete a Collection

1 Answers