CODEWITHSUNDEEP
jpajakarta-eeone-to-onejoin
JayC667
JayC667

Reputation: 2588

JPA cant delete @OneToOne if @JoinTable is defined

I have a funny 'problem' i.e. phenomenon.

Quick Infos:

WORKING EXAMPLE When I have an entity set up like this 

@Entity
@Table(name = Thema.TABLE_NAME)
public class Thema extends _BaseEntity {
    static public final String TABLE_NAME = UEntity.TABLE_PREFIX + "Thema";

    @Expose @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true) 
    private TopThema topThema;
}

I can simply EntityManager.remove() it.

.

EXAMPLE NOT WORKING:

But if it's defined like this

@Entity
@Table(name = Thema.TABLE_NAME)
public class Thema extends _BaseEntity {
    static public final String TABLE_NAME = UEntity.TABLE_PREFIX + "Thema";

    @Expose @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true) 
    @JoinTable(name = TABLE_NAME + "_topThema")
    private TopThema topThema;
}

(having an intermediate table "GP_Thema_topThema", as defined by @JoinTable(name = TABLE_NAME + "_topThema") then EntityManager.remove() throws an exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (test_PT_local.GP_Thema_topThema, CONSTRAINT FK_GP_Thema_topThema_topThema_ID FOREIGN KEY (topThema_ID) REFERENCES GP_TopThema (ID))

I know that it does not make much sense, to have an extra @JoinTable in use, when the reference could also just be saved inside "GP_Thema" as 'GP_Thema'.'TOPTHEMA_ID'. Plus: both Entity definitions just work equally well on the Java side.

But out of interest: why is JPA not able to remove the entry in the @JoinTable GP_Thema_topThema, even though I set cascade = CascadeType.ALL and orphanRemoval = true?

Update: And because of the @JoinTable I also cannot - delete TopThema first, then Thema (throws MySQLIntegrityConstraintViolationException on TopThema delete) - det Thema.topThema = null, then update (throws MySQLIntegrityConstraintViolationExceptionagain)

Upvotes: 1

Views: 284

Answers (2)

Carlitos Way
Carlitos Way

Reputation: 3424

As side note, you can delete your Thema entity using JPA and MySQL ... To do so, you just need to declare the foreign keys of your @JoinTable (GP_Thema_topThema) as "ON DELETE CASCADE" ...

Example:

CREATE TABLE GP_Thema_topThema(
    thema_ID integer NOT NULL,
    topThema_ID integer NOT NULL,
    FOREIGN KEY (thema_ID)
        REFERENCES thema (id)
        ON DELETE CASCADE,
    FOREIGN KEY (topThema_ID)
        REFERENCES top_thema(id)
        ON DELETE CASCADE
)

That way if the database receives the command of deleting a Thema entity, then any related GP_Thema_topThema entities (or tuples) will be deleted also ...

Upvotes: 0

JJCV
JJCV

Reputation: 326

Is not JPA not being able to delete the entity but the actual database. What you are looking for is a deferrable constraint and that is not possible in MySQL.

Mysql docs regarding defferable constraints here

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key

Have you considered switching to PostgreSQL?

DEFERRED constraints are not checked until transaction commit

Upvotes: 1

Related Questions