openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gunnar Grim (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2270) Wrong SQL reordering on DELETE causing foreign key constraint violations if relationships are nullified
Date Tue, 12 Nov 2013 12:46:19 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13820061#comment-13820061
] 

Gunnar Grim commented on OPENJPA-2270:
--------------------------------------

I think this bug report is a bit too narrow. In my experience, cascade delete will never work
when foreign keys are used in the database. OpenJPA seems to always delete the referenced
(parent) row before the referencing (child).

We have an application will several cascade delete relationships. It works fine in GlassFish
and JBoss but not in Geronimo. We don't want to use OpenJPA-specific annotations since it
must be portable.

> Wrong SQL reordering on DELETE causing foreign key constraint violations if relationships
are nullified
> -------------------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-2270
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2270
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: kernel
>    Affects Versions: 2.1.1, 2.2.0
>            Reporter: Givanildo Dantas Alves
>
> A Parent has a bi-directional one-to-many relationship to Child. A foreign key is added
to ensure consistency between PARENT and CHILD tables
> @Entity
> public class Parent {
>     @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
>     private Set<Child> childList = new HashSet<Child>(); 
> ...
> }
> @Entity
> public class Child {
>     @ManyToOne
>     @JoinColumn(name="PARENT_ID", nullable=false)
>     @ForeignKey(deleteAction=ForeignKeyAction.RESTRICT)
>     private Parent parent;
> ...
> }
> For a given Parent instance previously persisted in the database, I nullify the relationships
to its associated Child instances (i.e remove all Child's from Parent collection and have
those Child's no longer point to any Parent) and call EntityManager API to remove the Parent
and the Child's
> for (Child c : parent.getChildren()) {
>        parent.removeChild(c);
>        entityManager.remove(c);
> }
> entityManager.remove(parent);
> However, OpenJPA attempts to execute the SQL DELETE statement for PARENT table prior
to deleting the CHILD records, which causes a foreign key constraint violation:
> DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030,
DRIVER=3.61.75 {prepstmnt 1505188279 DELETE FROM ADMINISTRATOR.Parent WHERE PARENT_ID = ?
[params=(long) 301]} [code=-532, state=23504]SQLCA OUTPUT[Errp=SQLRI079, Errd=-2145779603,
0, 0, 0, -95, 0]
> DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030,
DRIVER=3.61.75
> FailedObject: com.ibm.dis.api.Parent-301
> The error happens regardless of the value configured in openjpa.jdbc.UpdateManager property
> I can only make deletion work fine if the relationships are not nullified



--
This message was sent by Atlassian JIRA
(v6.1#6144)

Mime
View raw message