CODEWITHSUNDEEP
hibernatejpa
rich
rich

Reputation: 19434

Constraint error on elementcollection deleting with JPQL

JPQL:

delete from Session where deviceId=:deviceId and username=:username

Error:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERROR: update or delete on table "edge_session" violates foreign key constraint "fkh7j6o58rfwfumainodrxptobt" on table "session_contactmethods"

Session class:

@Entity
@Table(name="EDGE_SESSION")
public class Session {

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

    @ElementCollection(targetClass=ContactMethod.class)
    @Enumerated(EnumType.STRING)
    private Set<ContactMethod> contactMethods;

...
}

Should I be adding specific CascadeTypes to the contactMethods field? Because the foreign table holds an enum, I'm assuming the delete should be ok not to happen, because I want that list of enums to remain?

EDIT: Looks as though the session_contactmethods table it creates isn't just the enum values, but a join key with the Session.

# \d session_contactmethods
        Table "public.session_contactmethods"
     Column     |          Type          | Modifiers
----------------+------------------------+-----------
 session_id     | bigint                 | not null
 contactmethods | character varying(255) |
Foreign-key constraints:
    "fkh7j6o58rfwfumainodrxptobt" FOREIGN KEY (session_id) REFERENCES edge_session(id)

# select * from session_contactmethods;
 session_id | contactmethods
------------+----------------
          1 | EMAIL
          1 | TELEPHONE
          2 | TELEPHONE
          2 | EMAIL
(4 rows)

Upvotes: 1

Views: 767

Answers (1)

Neil Stockton
Neil Stockton

Reputation: 11531

There are two ways to remove objects in JPA.

  1. Use EntityManager.remove(...). This will cascade as required dependent on the cascade setting.
  2. Use a Bulk Delete query. This will NOT cascade and you are basically saying "Trust me, I know what I'm doing"

You chose the latter, and it tries to do as you instructed and failed for the obvious reason that there is connected data. Use the first option, or remove the related objects first from the Session objects that are affected

Upvotes: 2

Related Questions