Hi,

I have defined a many to many relationship between two classes, viz.
Event and Person (defined in a separate database table person_event).
The code of these classes is listed at the end.

When I tried using HQL query to delete an event, it came up with an exception. What I don't understand is that delete(event) works, but the HQL query doesnt work and throws the foreign key violation exception.

-Abhishek

Below is the code excerpt.

String hql = "delete from Event where title = 'Your Event'";
query = session.createQuery(hql);
int rowCount = query.executeUpdate();
System.out.println("Rows affected: " + rowCount);
session.close();

And the EXCEPTION it caused.

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on "events" violates foreign key constraint "fkecd7dd303b90a48b" on "person_event"

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 18 more

Event and Person Class

package test;

import java.io.Serializable;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
@Table(name="events")
public class Event implements Serializable {
private Long id;
private String title;
private Date date;

public Event() {
}

@Id @GeneratedValue(strategy=GenerationType.AUTO)
public Long getId() {
return id;
}

private void setId(Long id) {
this.id = id;
}

@Temporal(TemporalType.TIMESTAMP)
public Date getDate() {
return date;
}

public void setDate(Date date) {
this.date = date;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}
private Set<Person> participants = new HashSet<Person>();

@ManyToMany(
cascade={CascadeType.PERSIST},
mappedBy="events",
targetEntity=Person.class
)
public Set<Person> getParticipants() {
return participants;
}

public void setParticipants(Set<Person> participants) {
this.participants = participants;
}
}

package test;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

@Entity
@Table(name="person")
public class Person implements Serializable {
private Long person_id;
private int age;
private String firstname;
private String lastname;

public Person() {}

@Id @GeneratedValue(strategy=GenerationType.AUTO)
public Long getId() {
return person_id;
}

public void setId(Long id) {
this.person_id = id;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

public String getFirstname() {
return firstname;
}

public void setFirstname(String firstname) {
this.firstname = firstname;
}

public String getLastname() {
return lastname;
}

public void setLastname(String lastname) {
this.lastname = lastname;
}

private Set<Event> events = new HashSet<Event>();

@ManyToMany(
targetEntity=Event.class,
cascade={CascadeType.PERSIST}
)
@JoinTable(
name="person_event",
joinColumns={@JoinColumn(name="person_id")},
inverseJoinColumns={@JoinColumn(name="event_id")})
// Defensive, convenience methods
protected Set<Event> getEvents() {
return events;
}

protected void setEvents(Set<Event> events) {
this.events = events;
}

public void addToEvent(Event event) {
this.getEvents().add(event);
event.getParticipants().add(this);
}

public void removeFromEvent(Event event) {
this.getEvents().remove(event);
event.getParticipants().remove(this);
}
}

I think that Hibernate doesn't take care of anything else but the query when using HQL, so it doesn't generate the query to delete the related rows on person_event table when deleting event objects.

It seems that the logic behind HQL delete and update is much simpler than the one behind session.delete(object).
So as pepelnm said above, hql delete will not care about your relation table and consequence of deletion.

However if you really want to do your delete using HQL,
you have to tell the Database it must delete row(s) in child table when corresponding row in parent (ie referenced) table are deleted.

example with MySQL:
ADD CONSTRAINT FKECD7DD30898B717B
FOREIGN KEY (event_id)
REFERENCES events(id) ON DELETE CASCADE;

ALTER TABLE person_event
ADD CONSTRAINT FKECD7DD30BDAE051C
FOREIGN KEY (person_id)
REFERENCES person(id) ON DELETE CASCADE;

It just tried it and it works perfect.

I do not want to generate the database schema by myself. I would want to do that using the annotated classes themselves.

Hi!
i have the same problem. i'm trying to map 4 entities with typical relationships. one of these is a linktable. I'm working on hibernate 3 over a postgres 8 database.

I've configured the mapping for cascade="delete" in all of the "manytoone" tags and all the "set" tags. when hibernate generates the tables and contraints , the foreing key actions are configured with NO ACTION (delete or update).

When i tried to run a delete operation, the exception is the same (update or delete on "searchcriteriatype" violates foreign key constraint "fkf1081d07efe0a597" on "searchcriteria")....

i've tried changing the foreing key actions manually over the database, but that doesnt matter because hibernate re-generate the tables ..

I'm using session.delete in this attempt, but i tried with a hql query like as "delete...."

this is my mapping for "searchcriteriatype"


i'm using a Home class to make this actions, this is the class


that's the other mapping,